Fun with Functions: Expenses tracking tutorial for Excel 2007

The ever-dependable official Excel Team blog has come up trumps again with a recession-themed tutorial showing how you can use the Table functions in Excel 2007 to track your household expenses.

Written by Excel software testing developer Monica Poinescu, the tutorial is designed to show off some of the new functions in Excel 2007.

Starting with a list of expenses sorted by dates and categories (food, travel, clothing, etc), the tutorial is based on an Excel table that uses structured references. It then applies a series of SUMIFS, AVERAGEIFS and COUNTIFS calculations to analyse the different expense trends within the table.

The way the table is set up, you can copy/paste these formulas down as you add more months to take account of new entries entered into the expense list.

A couple of readers were not impressed by the tutorial, asking "Why not use pivot tables?" and in the case of Neal O: "Has anyone found a compelling case to use Excel 2007 tables at all?"

Excel program manager Joseph Chirilov responded: "As is often the case with Excel, there's more than one way to skin a cat...

"The point of this blog post was to educate people on some of the new functions in Excel by way of example; it wasn't trying to make a statement about the ideal way to solve this particular problem. I totally agree though, if you are someone who is very familiar with PivotTables, then that might be a better solution for you."

The Fun with Excel functions series
1. OFFSET
2. ABS, SUMIF and the array formulae
4. New Excel 2007 IFERROR function

Also see ExcelZone Compendium: Functions & Formulae

Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.