Why you should be using the SUBTOTAL() function in Excel

I utilized the SUBTOTAL() function today and was thinking that this has to be one of the most important, and hopefully the most used, formula in the Excel formula arsenal. Why? Because any time a table of information is filtered and you are utilizing SUM(), MIN(), MAX(), AVERAGE(), or others, these standard total formulas do not change with the filtered data. They retain the range originally given to it in the formula.

Here is an example.

Using Northwind sample data, I created a SUM() of Sales total.

Notice how the SUM() of sales does not change when the rows are filtered.

One thing to note, Excel 2007 is smart enough that IF you use the

AutoSum function, it will utilize the SUBTOTAL() formula. If you or your users are filtering data, be sure that the SUBTOTAL() function is being used.

 

Here in this example I am using the SUBTOTAL() function instead of the SUM() function. Notice how the total for sales now represents the filtered amount.

How does the SUBTOTAL() function work?
SUBTOTAL(function_num,range)

The function_num represents the type of function you want to perform as a SUBTOTAL() function. Here is a list from Microsoft’s help screen:

In my above example I used: SUBTOTAL(109,[Sales])

109 represents SUM ignoring hidden values (9 would have worked fine here since I didn’t have any hidden values, I had filtered values).

[Sales] happens to represent table column linked to an Access Database. This could be any range however.

 

So the next time you are working on an Excel spreadsheet and you need to have some dynamic totals based on filtered data within the spreadsheet, look to the SUBTOTAL() function.