UseExcel - Track your expenses.

Use Microsoft Excel to record and manage your personal expenses. Take advantage of the AutoComplete option to simplify data entry.

Worksheet design for tracking personal expenses

  1. Start with a project title in cell A1. Call it Personal Expense Tracking.
  2. Drop down to row 2 and add some column titles starting in cell A2. Call the titles Date, Payee Category, Memo, Expense, Revenue, Balance. The last title should be in cell G2.
  3. Add your first expense item in row 3.
    Excel Expense Tracking 1st row
  4. Add the formula for the Balance in cell G3. Normally the balance for today equals yesterday's balance plus the current line expense minus the current line revenue. But since row 3 is the very first row of detail, there isn't any balance from yesterday. So the formula in cell G3 is just =E3-F3
  5. Add your second expense item in row 4.
  6. Add the formula for the Balance in cell G4. Now we can apply the usual formula for balance which equals yesterday's balance plus the current line expense minus the current line revenue. The formula in cell G3 is =G3+E4-F4 The reasoning for calulating expense minus revenue instead of revenue minus expense (which we are more used to seeing) is that the main purpose of this Excel project is to track expenses. There will be many more expense items than revenue, and calculating expense minus revenue assures that we will see a positive balance, which is more intuitive than a negative one. One way a revenue item can arise is if you buy 4 ballet tickets, 2 of which are for someone else. If you are paid back for the 2 tickets, this should count as revenue. Another source of revenue is a refund for a purchase.
  7. Now that you have the usual balance formula, grab the fill cursor in cell G3 and fill down column G as far as you want to go. This is what your project should look like:
    Excel Expense Tracking with more rows
  8. How to keep the column titles in view at all times. After about 20 rows of expense 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.
  9. Tip - Use AutoComplete to ensure consistent expense 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.
  10. For an extra effort, you can hide those ugly balances in column G 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 G, but if there is a date, we will put the balance in column G. The formula for the balance in column G for rows 4 and below is =IF(A8="","",G3+E4-F4)
  11. You might also want to protect cells that should not change. Such as the headers and titles, as well as column G 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. ExpenseTracking.xls

[Top of Page]