UseExcel: Track & balance your checking account.

Use Microsoft Excel to record and manage your personal checking account. A couple of years ago I had the choice of buying off-the-shelf checking account tracking software for $100, or building my own for free using Excel. Guess which option I chose!

Worksheet design for tracking your checking account

  1. Start with a project title in cell A1. Call it Checking Account for ... [fill in your name].
  2. 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.
  3. 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.
  4. 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".
  5. Add the formula for the Balance in cell H5. The formula in cell H5 is =H4-F5+G5
  6. 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:
    Excel Checking Account Tracking with more rows
  7. 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.
  8. 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.
  9. 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 =IF(A4="","",H4-F5+G5)
  10. 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.
Here is the completed Excel workbook.
Click the Save button when the File Download box pops up. CheckingAccount.xls


[Top of Page]