#163: Become a Superhero with Excel Subtotals

Fasten your seat belts because this week we’re going to dish out an extra large and powerful dose of Excel ‘magic’. I’m a HUGE fan of the AutoFilter tool in Excel. If you’re not familiar with it, filtering gives you a quick, easy way of paring down your data to focus on only the stuff that matters. Go ahead and take a look here….we’ll wait ’til you get back. (Really, go ahead!)

OK. Welcome back. We now continue with our regularly scheduled programming.

AutoFilter by itself can be a big time-saver and by paring it with the SUBTOTAL function you can turn yourself into an Excel Superhero. The SUBTOTAL function lets you do dynamic calculations that change based on only what is displayed by your AutoFilter settings. That means if it’s not visible, it’s not included in the calculation. Here’s a sneak peak…

Subtotal & Autofilter in action.

Subtotal & Autofilter in action.

The syntax for the SUBTOTAL function is as follows:

SUBTOTAL(function, refs)

In this syntax, the function is a value of 1 through 11, each representing a different way that SUBTOTAL can analyze your data. The settings have the following meanings:

Function    Meaning

1         AVERAGE

2         COUNT

3         COUNTA

4         MAX

5         MIN

6         PRODUCT

7         STDEV

8         STDEVP

9         SUM

10         VAR

11         VARP

How It Works:

Using this sample data I can create one set of formulas to calculate stats on ANY combination or the data I want.

[ Download a copy of my spreadsheet to play with yourself ]

Here are the formulas I created:

..and the results of those formulas when the AutoFilter is displaying all the data.

Same formulas but different results with the AutoFilter displaying only the Asia Region:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s