Worksheet design for tracking your checking account
Here is the completed Excel workbook.
Start with a project title in cell A1. Call it Checking Account for ... [fill
in your name].
Drop down to row 2 and add some column titles starting in cell A2. Call the
titles Date, Num, Payee Category, Memo, Payment, Deposit, Balance. The last
title should be in cell H2.
Add your first expense item in row 3. Since we rarely start this Excel project
with an empty checking account, row 3 should just have a date, a memo item
called "Starting Balance" and the actual balance as of the date entered in cell
A3. So the balance in cell H3 is a numerical value, not a formula.
Add your first actual checking account transaction in row 4. For the Num column
(column B) enter either a check number, or "EFT" for "Electronic Funds
Transfer" or "ATM" for "Automatic Teller Machine".
Add the formula for the Balance in cell H5. The formula in cell H5 is
Now that you have the balance formula, grab the fill cursor in cell H5 and fill
down column H as far as you want to go. This is what your project should look
like after several lines of entry:
How to keep the column titles in view at all times.
After about 20 rows of account details, the column titles in row 2 will scroll
out of view. To prevent this, select cell A3 and go to the Windows menu on the
Standard toolbar, and click on Freeze Panes.
Tip - Use AutoComplete to ensure consistent account category spelling.
AutoComplete is turned on by default in Tools, Options, Edit tab. When you want
to summarize the details later on, the summary will be more accurate with more
consistent category spellings.
For an extra effort, you can hide those ugly balances in column H where the
rows are otherwise empty. We want to test, with the IF function, to see if
there is a date in column A. If there isn't, we will put an empty string in
column H, but if there is a date, we will put the balance in column H. The
formula for the balance in column H for rows 4 and below is
You might also want to protect cells that should not change. Such as the
headers and titles, as well as column H which has the balance formulas. To do
this, select the cells that you want to edit, Format, Cells, Protection, Unlock
the cells. Then go to Tools, Protection, Protect Sheet.
Click the Save button when the File Download box pops up.