Creating a Check Register With Excel

A 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.

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:

The mouse-click sequence would be:

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:

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

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!

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:

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."

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:

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:

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

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

Our Register labels should now appear something like this:

Now we're down to the last bit of formatting: Resizing a few of our 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:

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.

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

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

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.

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

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.

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

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

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.