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.
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.
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.
Enter all the items, then create a pivot table to summarize the spending.
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.