Creating a Check Register With Excel (Part 3)

In Part 2 of Creating a Check Register with Excel, our Check Register spreadsheet was really taking shape:

About all that's left is to insert some formulas into Column H — our "BALANCE" column — so that Excel can do the necessary math and display our account's running balance.

Any geek can tell you: Formulas are where Excel gets its number-crunching power. The whole idea of this exercise is to make a spreadsheet so that Excel can help us keep tabs on our checking account balance. It's time now to learn a little about formulas, and build one that does what we need.

Since Column H ("BALANCE") is where we need Excel to do the math for us, that column will be our focus. Here's how things look now:

It's important to note that we don't need a formula in Cell H2. That cell is reserved for our account's opening balance, which the user must enter by hand. (There's no way Excel could know our account's opening balance, right?)

Let's begin by moving to Cell H3. Once there, type in this formula:

Once you've entered the formula above (exactly as shown!), press either your [TAB] or [ENTER] key to leave Cell H3. The formula now resides there ... behind the scenes, in a way.

Basically, by entering this formula, what we're telling Excel is this:

In Cell H3, show us our Opening Balance (H2) minus our next debit (F3), if one exists, or plus our next credit (G3), if one exists.

I created the formula this way so that it accounts for the possibility of either debits or credits in any row. I can't think of a reason why you'd ever have both items in a row, but even if you did, the formula could handle it.

Cell H3 now shows $165.81, which is $200 (our Opening Balance) minus $34.19 (our first debit, in Cell F3) plus $0 (our nonexistant credit in Cell G3).

Now let's move our cursor back into Cell H3. The cell shows us $165.81, of course, but look at the Formula Bar above the spreadsheet. There's our formula!

Formula Bar: Excel's Formula Bar typically resides just above the column headers. Place your cursor in any cell, and if there's a formula in that cell, it'll show up in the Formula Bar. You may enter formulas directly into the cells themselves, or by placing your cursor in a cell (making it the so-called "active cell") and then clicking the Formula Bar and typing your formula there.

Learn more about the Excel's Formula Bar by clicking Excel's Help feature and searching for "formula bar."

Now let's make Cell H4 our active cell. In it, enter the formula:

...And press [ENTER] to enter the formula and leave the cell. Now you should have:

Excel gives us a new balance of $136.31. Using our formula, that's the sum of: $165.81 (cell H3) minus $29.50 (cell F4) plus $0 (Cell G4).

And finally, for Cell H5, enter the following formula:

Then press [ENTER], and you'll get:

Three transactions into our Register, and we haven't had to use a calculator even once. Pretty cool, huh?

But here's where our current formula breaks down: If we were to carry it down a cell or two, anticipating more transactions to come, we'd have something rather ugly:

... And that is not what we want. Sure, $964.01 is our true balance until more transactions come along. But it looks crappy, right? We certainly don't want crappy.

Head back up to Cell H6 — the first blank cell in Column H. In that cell, enter the formula:

Then press [ENTER]. You'll now have a "blank" Cell H6, yes, but the cell's not truly empty, as we see in our Formula Bar:

In plain words, the formula above tells Excel this:

Cell B6, of course, is our "DATE" column. Once you enter data there, the formula will do its thing. In row 6, let's make an entry for an ATM withdrawal of $20, and see what happens:

Since every single transaction that we might enter into our Register should have a date, we can be pretty comfortable basing our "IF(ISBLANK)" formula on what's found in Column B. But what if we wanted to base our formula on whether or not our DEBIT and/or CREDIT columns are blank? Well, we could do that, too. In that case, for Row 7, Column H, the formula would be like so:

Or, in OpenOffice Calc, the formula would be:

The only thing that changes with this formula is that the BALANCE column will be blank until data is typed into either the DEBIT or CREDIT columns (Columns F and G, respectively). In my opinion, this formula is better aesthetically — but the date-based one would work, too.

Right now you're probably thinking:

Wow! That's not so hard! But I want to use this Register for more than ten or twelve transactions. I'm going to have rows and rows of transactions! I don't want to have to type a formula into hundreds of cells in Column H! That sucks! I QUIT!

Hold on there, Speedy. You won't have to type that formula into hundreds of cells. Remember that Excel is (in my humble opinion) one of the most powerful programs out there. The guys and gals who put the thing together knew what they were doing. They found a way to overcome the tediousness of entering iterations of formulas over and over again. And they named this glorious feature AutoFill.

AutoFill: You'll want to get really well-acquainted with this Excel feature, as it will save you untold amounts of time and keypunching tedium.

AutoFill can be used for a multitude of things, but perhaps its most-helpful use is to automatically fill (get it? "autofill") formulas into adjacent cells once you've created the first formula.

When learning to use AutoFill, you need to know that the "AutoFill Handle" is the small square at the bottom right corner of the active cell. The "AutoFill Cursor" looks like a large, thin plus sign; it's what your normal Excel cursor changes to when you've engaged AutoFill by clicking on, and then dragging, the AutoFill Handle.

Once you've created a formula and you're ready to "AutoFill" it into adjacent cells, simply make the formula's cell the active cell. Then click and hold the AutoFill handle. Drag your cursor (which now should look like a plus sign) into the cells in which you want Excel to carry your formula.

Do it just right, and Excel will automatically fill-in the formula through all the cells you specify — and it'll adjust the row numbers and column letters on its own!

So let's take the formula we created for Cell H7 and use AutoFill to save us the trouble of typing it into the next hundred or so cells in Column H.

Here again is the formula to enter into Cell H7. If you haven't already typed it there, or copy 'n' pasted it there, do so now:

Once the formula's there, Cell H7 should still "look" empty. It's waiting for your data to hit either Cell F7 or G7.

Now, with Cell H7 still the active cell, move your cursor to the AutoFill Handle. Your cursor will change to a thin plus sign. Now left-click and hold the AutoFill Handle and drag it all the way down through, say, Cell H100 (that's Column H, Row 100).

Once you've dragged down through Cell H100, let go of the left-click. The formula from Cell H7 has now been filled-in to every cell all the way to H100, and Excel has automatically changed each Row number and Column letter as necessary!

Click in any cell in Column H — well, somewhere between Rows 7 and 100 — and you should see a familiar formula in the Formula Bar:

Now ... how much typing did AutoFill just save you? Bunches!

Once you've carried the Column H formula down to whatever row you like — I sort of picked Row 100 arbitrarily — then your Check Register spreadsheet is almost done. There's just one more thing to knock out:

At this point, it's all about Protection!

Usually, once you've added formulas or headings to cells, you sure don't want them to accidentally get overwritten. Excel provides a way to keep these special cells safe. It's called "Locking the Cell" and then "Protecting the Worksheet."

(Actually, all cells are already "Locked" by default. So it's more like "Unlocking Cells You'll Be Typing In" and then "Protecting the Worksheet.")

For starters, we need to select all the cells into which we might need to enter data. If you carried the formula above all the way down to Cell H100, this would mean we need to select a big range of cells. That range would stretch all the way from Cell B2 to Cell G100.

Place your Excel cursor in Cell B2, and then left-click to make it active. Now left-click again in that cell and drag your cursor all the way across to Column G. Then, still holding the click, drag down to Row 100. You will now have selected the range from B2 to G100, and every cell in the selected range should now be a slightly darker color, like this:

Once you've selected the range from B2 to G100, you will want to right-click inside it. In the menu that pops up, select FORMAT CELLS.

When the next menu comes up (see below), select the "Protection" tab. Then uncheck the box that says "Locked" and then click OK.

You've now UNLOCKED all the cells in the range from B2 to G100. We've done this because all these cells could potentially need data to be entered into them as you use your Check Register. Alternately, all cells with the Protection status of LOCKED (which is the default) will not be able to be typed in ... once we activate "Protection" for this worksheet!

So now let's "Protect" the worksheet. In versions up through Excel 2003, the mouse-click progression is:

In Excel 2007, the menu progression is:

Now the "Protect Sheet" menu should appear:

Leave the password field blank.

Then, personally, I like to uncheck the box by "Select locked cells." But whether it's checked or not, once you turn on Protection by clicking the OK button here, the user won't be able to type in any of the Locked cells. Since that's what we want, click the OK button now. The worksheet is now Protected, and our formulas and headings are safe from any nasty erroneous keystrokes.

And that's it — your Excel Check Register is pretty much done. Oh, there are always more things you could do, of course, like hiding unused columns and applying alternate-row shading to your worksheet. But we'll save those for another time.

If you'd like to download the spreadsheet I created during this tutorial, you can get it here:

And please remember that I do offer a slightly-more-sophisticated free Excel Check Register spreadsheet, as well as a not-free, but way-more-sophisticated Excel Check Register with sorting, reporting, and reconciling features.