How to Maintain Accurate Subtotals When Your Data Set Expands in Size

Subtotal in Excel 2010 Table

This is Part 2 of my series of video tutorials demonstrating how to use the SUBTOTAL Function in Excel.

In Part 1, I showed you the value of using the Subtotal Function to summarize the results of applying a Data Filter to a range of cells.
In this part, I show you how to use an Excel 2007 or Excel 2010 Table to ensure that your Subtotal Formulas are automatically updated when you append records or add additional fields to your original data set.

I strongly recommend basing Filtered Lists and Pivot Tables on an Excel Table (in Excel 2007 or 2010) or an Excel List in Excel 2003. This way, any formulas, filters and references that you make will be automatically updated when you append additional records or otherwise change the structure of your data set.

Function Numbers 101 through 111

Notice that when you “toggle on” the Total Row for a Table or List that Excel uses this formula = SUBTOTAL(109, Table1, [Sales]). Function 109 will use the SUM Function(109) to total the values in the “Sales” field ([Sales]) of a Table named “Table1.” These Function Numbers + 100 were introduced in Excel 2003 and the are automatically applied whenever you are using a Total Row in an Excel Table.

I think that you will learn some cool tricks in this lesson. Let me know what you think!