Master Excel Tables with AutoFilter and Subtotal

Posted on April 9, 2008 at 5:23 am

How much of Microsoft Excel are you using? I thought so. Get ready because we’re stepping it up another notch. Excel is a powerful but often underutilized tool. Many of us use it every day to plan and track budgets, analyze marketing data, and occasionally to do more important things such as manage the office March Madness pool. Several Online Tech Tips posts have already covered clever but unfamiliar functions like VLOOKUP and SUMIF. Now it’s time for another round of Outrageous Excel Tips!

I’m going to illustrate two Excel features which, used in concert, make tabular analysis a snap. Tabular data is what you get from a database extract or CSV file. The AutoFilter and oft-overlooked Subtotal functions, like peanut butter and chocolate, are meant to be used together. They provide an easy way to “code” a spreadsheet to do subsets, sums, averages, counts, and more.

The Excel table below is an extract of our company’s purchases by department. We have a typical “header” row, followed by 11 data rows. You can see the functions I included for this table: Sum, Count, Min and Average. More about them later.

Now, the boss comes in and needs to see just how much advertising airtime the Marketing people are purchasing, and how much we are paying. AutoFilter gives us the power to make entire rows visible or invisible based on cell contents. The Subtotal function updates the Sum, Count, Min and Average accordingly. Let’s quickly add an AutoFilter.

Excel AutoFilter

To add AutoFilter, select the entire range of cells, including the header row containing the column labels (A1:F12). Then use Data -> Filter -> AutoFilter. Now you can see the filter indicator above each column in the table:

The neat thing is this: click on any column heading, and AutoFilter gives you a drop-down list of values in the column for you to select from, and filters the entire table for the result:

The lists are dynamic based on table contents, so adding a row or changing an existing value means the drop-down list is updated also. You can show all values (default), select just one value, or create a custom filter based on boolean logic. In this example we need to select the “Airtime” value.

The result is:

AutoFilter acts by making rows visible and invisible. The original eleven data rows are still present, although some are invisible based on the selected filter. A2:F12 is still data range.

We quickly see the two “Airtime” buys in this dataset, and the average price paid is $5000. Use the drop down filters to select all values again, or select a different value, and do it on any number of columns. Depending on your table, some columns will make sense for filtering and others may not.

Excel Subtotals

In this example, the values for Sum, Count, Min, and Average update based on the filter automatically because of the powerful Subtotalfunction. Subtotal operates only on visible rows of data in a range, so it’s great in combination with AutoFilters. Subtotal is also simple to set up. The syntax for Subtotal is:

=Subtotal(function#, range)

Okay, you do have to know the “function #” which is a pain. Are you listening MicroSoft? Here is the list of commonly used functions with Subtotal. Others can be accessed by searching Excel Help: