Tutorials


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!

Step 5: Input a little test data.

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:

Click to enlarge image.

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

Step 6: Clean up the spreadsheet's formatting.

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.

Select Column B by clicking its header.

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:

NUMBER → DATE → 03/14/01 Format cells to show dates uniformly.

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

The 'center' button.

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

Column B shows dates correctly, and centered.

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:

Column C (ITEM #) is now centered.

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.

Select Columns F, G, and H.

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

NUMBER → ACCOUNTING → DECIMAL PLACES: 2

And click OK.

Format cells to show dollar amounts uniformly.

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:

FONT → COLOR (DROP-DOWN MENU)

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!

Our data is formatted nicely!

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

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