Tutorials


Creating a Check Register With Excel

Excel TutorialA frequent request received here at It's Your Money is, "Can you show me how to make my own check register in Excel?"

Now, I offer a free check-register spreadsheet, an "Envelopes" Check Register, and a fancier, for-pay check register with sorting. The more I consider it, though, the more I think that building a tutorial like this could be a great way to show Excel beginners some of the basic workings of the program.

Screenshots here will be from Excel 2007. Other versions of Excel will appear slightly different, but the functionality is essentially the same.

Step 1: Open a blank Excel spreadsheet.

Our first item of business, of course, is to open up a new, blank Excel spreadsheet. Typically you'd do this from the START menu:

Start Excel.

The mouse-click sequence would be:

START → PROGRAMS → MICROSOFT OFFICE → EXCEL

At that point, you should have a blank Excel spreadsheet opened on your screen.

If you're not real familiar with Excel's layout, here's what's important: In Excel, "rows" run horizontally, and are ordered by number. "Columns" run vertically, and are ordered by letters:

Excel Rows, Columns, and Headers

Each box where a row and column intersect is called a "cell." In this screenshot, the "active" cell is B2 (Column B, Row 2):

Excel Cell B2

It's what you put in these cells — names, numbers, pictures, formulas — that makes spreadsheet programs like Excel work their magic ... as we're about to see!

Step 2: Create Your Column Labels.

Once the blank spreadsheet (or "worksheet") is opened, we'll start by adding a handful of column labels — the same ones you'd find in your paper check register. Let's start with cell B1 (that's column B, row 1). Enter the word "DATE" and then move right, to the next cell (C1). Enter "ITEM #" here. Then fill in the next five cells until your sheet appears as follows:

Enter column labels.
Step 3: Format the Column Labels.

Next I'm going to do a bit of formatting on the labels. I'll left-click cell B1 ("DATE") and then drag the cursor through the last label — it's in cell H1 ("BALANCE"). In spreadsheeting lingo, this is called "selecting a range."

Select range of cells (column headers).

I'm "selecting this range" because I would like to make these headers show up in a bold font. We accomplish this by clicking the "B" (bold) formatting button:

The bold-format button.
Format the header cells in bold font.

I'd also like to give my row of register labels a background color that's different from the rest of the worksheet. To accomplish this, I click the header for Row 1. (It's circled in red below.) Now every cell in that row is selected:

Select an entire row (range).

Find and click the background-formatting button; it has a paint-bucket icon on it:

The background-color formatting button.

This pulls up a palette from which you can select your desired background color. I'm going to use a shade of purple.

Open and select from the background-color palette.

Our Register labels should now appear something like this:

Labels mostly formatted!

Now we're down to the last bit of formatting: Resizing a few of our columns.

Step 4: Resize Some Columns.

Look at our columns, and think ahead for a moment: Chances are, a few of them simply won't be wide enough to hold the data we'll need to put in them. Our "PAYEE" column, for instance, could hold some pretty long names, and our "MEMO" column (which is meant for any notes you'd like to enter concerning the given transaction) could easily be even longer. And Column A — I use it as just a kind of "spacer" column — could be made more narrow.

Fortunately, Excel can handle these situations easily.

Let's start with Column A. It won't ever hold any data, and is mostly just an aesthetic thing — I don't like my tables to run right up against the left side of the page. But to conserve viewing space, I'd like to make Column A pretty narrow. Let's click the header for Column A, which effectively selects the entire column:

Select Column A by clicking its header.

We're going to change the width of this column. There are several ways to accomplish this. Because I know the exact width I'm looking for, I'm going to use menus to get it done.

In Excel 2007, using the HOME ribbon, click the FORMAT button, and then select COLUMN WIDTH.

Select COLUMN WIDTH from the FORMAT menu (Excel 2007).

In the COLUMN WIDTH window that comes up, change the width to 2. Click OK. Now Column A is quite narrow:

Column A is now narrow.

Next we need to widen Columns D ("PAYEE") and E ("MEMO"). First, select Column D by clicking its header.

Select Column D for resizing.

Now place your cursor on the border between Columns D and E. When you're directly over this border, your cursor will change into two arrows. This is another, often-simpler way to resize columns and rows — with the resizing cursor.

When your cursor is between the headers of Columns D and E, and the resizing cursor appears, left-click and drag your mouse to the right. This will widen Column D. Stop when its width is roughly 23 ... or whatever width you prefer.

Column D is now much wider.

Perform the same widening procedure on Column E ("MEMO").

Widen Column E also.

For one final formatting change to our Register labels, let's center each label in its respective cell. Once again, click Row 1's header. This will select the entire row.

Select all of Row 1 once more.

Now we want to center the text labels in each cell in Row 1. We do this by clicking the "center" formatting button ...

CENTER formatting button.

... which centers the contents of all currently-selected cells (in this case, Row 1):

Center the contents of our label cells.

Our Register labels are now created, and we've formatted them enough to make them look pretty decent. Now it's on to the really fun stuff: adding formulas and data to our spreadsheet, and letting Excel do its thing!

We'll work on those items in Part 2.

Learn About Excel

If you'd like to learn more about Excel, I highly recommend the Excel Bible series. The author, John Walkenbach, absolutely knows his stuff ... and his writing style is superb!

  1. Excel 2007 Bible
    John Walkenbach
  2. Excel 2003 Bible
    John Walkenbach