Creating a Check Register With Excel (Part 2)

In Part 1 of Creating a Check Register with Excel, we set up our column labels and gave them a bit of formatting. Right now, the top of our spreadsheet should look something like this:

Our next step, then, is to type in a few lines of test data. Let's get going!

This Check Register spreadsheet is going to work just like your paper register — except Excel will do all the yucky math for us. As you would expect, each of our columns has a purpose:

• DATE: The date of the transaction.
• ITEM #: The check number, or type of tranaction (ATM, deposit, etc.).
• PAYEE: The party to whom you wrote the check (or otherwise paid or received money).
• MEMO: Any notes you care to make about the transaction.
• DEBIT: If money left your account (outflow), you'd enter the amount here.
• CREDIT: If money came into your account (inflow), you'd enter its amount here.
• BALANCE: The current running balance in your account, given all transactions to date.

With all that in mind, let's plug a few rows of test data into our spreadsheet.

Here's what we'll plug in:

Those four rows of fake transactions will give us (and Excel) some numbers to play with as we progress.

One of the first things you should notice about our test entries is that they're not correctly formatted. Put simply, everything looks awful! The dates all look different; the "ITEM #" column isn't centered; the dollar amounts don't look like dollar amounts.

Let's take a moment and fix all these things — it isn't tough to do!

First we'll work on Column B — our "DATE" column. Click its header to select the entire column.

We want that column to show our dates in a uniform fashion. As for me, I'm a fan of the "month/day/year" format, so let's use that. While the column is selected, right-click on it, and choose FORMAT CELLS.

When the FORMAT CELLS window appears, select:

Now all the dates should look alike. While we're at it, let's center them in their cells. With Column B still selected, click the "center" button ...

... a couple of times. You should now have this:

About the only thing Column C ("ITEM #") needs is to be centered. Select the entire column, and click the "center" button twice. You'll then have:

Our columns for PAYEE and MEMO should be pretty much okay. Their contents will be left-aligned by default, which works nicely.

Columns F, G, and H ("DEBITS," "CREDITS," and "BALANCE," respectively) do need some help. Let's select all three columns. Select Column F's header with a left-click, and then drag all the way across to Column H. Now all three columns should be selected.

Again, right-click the selection and then choose FORMAT CELLS. Then:

And click OK.

Now our dollar amounts should look like dollar amounts, complete with dollar signs. My final change — and this is simply a matter of personal preference — is to have my DEBITS (Column F) appear in a red font.

To accomplish this, I click Column F's header to select the entire column. Then right-click on the selection itself. Select FORMAT CELLS. And:

Select red from the drop-down menu. Click OK.

At this point, all of Column F (including the "DEBITS" label) shows in a red font. I don't really want the label itself in red, though. So I select that cell only (F1). Following the same steps above, I change its font color back to black.

After that, my formatting is complete!

It's time to head to Part 3, where we'll discover the true power of Excel: formulas!