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.)

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

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.

ExpenseTrack02

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!

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. The last column calculates the year and month, so it’s easy to summarize by month. You could even get fancy, and add data validation to the Category column, with a drop down list of valid categories.

ExpenseTrack03

Enter all the items, then create a pivot table to summarize the spending.

ExpenseTrack04

Chandoo’s Excel School

A short reminder that Chandoo has opened a new session of his online Excel School. It has many new features, including a live webinar, and a full year of access to the course materials. Registration closes on September 29th, so check it out today, by clicking this link to Chandoo’s Excel School, or click the image below.

Chandoo Excel School

____________

You may also like...

5 Responses

  1. Martin says:

    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. Gregory says:

    In trying to figure out this spreadsheet, it took less than five minutes before I felt a headache coming on. Good thing it works for Lauren.

  3. @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.

  4. Bob Ryan says:

    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.

  5. Matt says:

    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 to Gregory Cancel reply

Your email address will not be published. Required fields are marked *