Help Improve This Excel Expense Tracker

On the Consumerist website last week, they posted Lauren’s Excel budget template, so I downloaded it, to take a look.

I’d call it an Expense Tracker, rather than a “Budgeter”, because it’s used to record income and expenses. (Do you know the origin of the word “budget”? I had to look it up.)

Expense Tracker Formula

Here’s what it looks like, with part of the formula for the Total cell showing in the formula bar.

The grey fill colour is added with conditional formatting.

ExpenseTrack01

Excel Formula for Total

Shown below is the full formula for the Total.

You can see that Lauren has named the date headings (_8_10d) and hidden total row (_8_10) for each month.

Long Excel Formula for Total
Long Excel Formula for Total

So Many Named Ranges

Wow! It makes me tired just looking at that. Lauren created a lot of named ranges, to set up the file, and she’ll need to do more work to add more months.

Because there’s a separate section for each month, her formula needs a SUMIF formula for each range.

She might have to upgrade from Excel 2003, or she’ll pass the character limit for that formula.

Room for Improvement

I don’t know who Lauren is, but she should be commended for setting this up, and keeping track of her income and expenses.

Sure, there are many ways to improve her Budgeter, but it seems to work okay, even if it is a bit convoluted. At least she knows where her money is going!

What Would You Do?

But, there must be better ways to keep track of income and expenses. How would you set up an Excel workbook to do this?

I’d probably create a simple list, with columns for Date, Item, Location, Category and Amount, like the table in the screen shot below.

The last column calculates the year and month, so it’s easy to summarize by month.

add data validation to the Category column
add data validation to the Category column

Add Drop Down Lists

You could even get fancy, and add data validation to the Category column, with a drop down list of valid categories.

Next, enter all your budget items, then create a pivot table to summarize your spending.

ExpenseTrack04

____________

0 thoughts on “Help Improve This Excel Expense Tracker”

  1. Debra,

    as always, excellent post.

    My first expense tracker was a similar kind of mess, and it somehow worked.

    I had this particular, nasty idea: to track EVERY kind of expense, and allocate it EXACTLY on the month that it should be paid, particularly applicable to credit card expenses.
    With 5 credit cards, in at least 2 currencies (ARS and USD), each with a different dates for month closing and payment, it was kind of tricky, and I promised myself that when the tides calm down a little bit, I’ll take that project back and eventually finish it !!

    Regards,

    Martin

  2. @Martin, thanks, glad you liked the post. I hope you found an easier way to keep track of your spending, and pay those credit cards on time!

    @Gregory, maybe Lauren will get some great ideas in response to sharing her workbook, and will go with something easier.

  3. Debra – FYI, my book helps folks develop their own “checkbook.” But the more experienced folks can download a “checkbook” or “income and expense tracker” file from the Free Downloads link on my site (SimplyLearningExcel.com) which already contains pivottables and charts.

  4. Wow..that’s pretty complex. I might have a crack at something similar myself and see what I can come up with – I’m sure it doesn’t have to be this complex! Still, if it works…

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.