Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader Alan B. wins an autographed copy of Learn Excel 97-2007 from CFO.com and MrExcel for his suggestion about using the =SUBTOTAL function in conjunction with the Filter command.
Most people run into the =SUBTOTAL function when it is added through the use of the Data Subtotals command in Excel. However, the SUBTOTAL function has another neat trick when used in combination with the Filter dropdowns in Excel 2007/2010 or the AutoFilter dropdowns in Excel 97-2003.
Easy Way to Apply a Filter
Normally, you would choose Data, Filter, AutoFilter in Excel 2003 – or Data, Filter in Excel 2007/2010. You would then have to select from the dropdown. There is a faster way to set up a filter starting in Excel 2007.
In Figure 1, you can filter to all of the Connecticut records by finding a cell that contains CT. Right-click that cell, choose Filter, Filter by Selected Cells Value.
The Filter will be applied and you will end up with only the CT records.
Adding the Subtotal Function
Select the first blank cell beneath one of the numeric columns, as shown in Figure 2.
Click the AutoSum button (Figure 3) or press Alt+Equals.
Normally, the AutoSum button adds a =SUM() function. However, since this data has been filtered, the AutoSum button provides =SUBTOTAL(9,C2:C413). This formula adds only the visible cells from the filter (See Figure 4, below).
Use the filter dropdown in B1 and change to a new state. The total will change to reflect only the visible records (See Fig. 5, below).
Subtotal Counts, Averages, and More
While the AutoSum button provides the version of SUBTOTAL that will sum, you can change to any of these 11 calculations. Simply change the 9, to one of these values:
1 – AVERAGE
2 – COUNT (Numeric cells only)
3 – COUNTA (All non-blank cells)
4 – MAX
5 – MIN
6 – PRODUCT
7 – STDDEV.S
8 – STDDEV.P
9 – SUM
10 – VAR.S
11 – VAR.P
Using SUBTOTAL with Manually Hidden Rows
The subtotal examples above assume that you are working with a data set that has rows hidden due to a Filter. What if you’ve manually hidden rows? Then you need to add 100 to the first argument. In Figure 6, the =SUBTOTAL(9 becomes =SUBTOTAL(109. The arguments 101-111 will deal with a data set where you’ve used Format, Row, Hide to hide the rows.
CFO Contributor Bill Jelen is the author of 32 books about Microsoft Excel including Pivot Table Data Crunching. You have the chance to win a copy of one of his books by posting a question to the Community Center at the right. If Bill selects your question as the topic of a future column, we’ll send you a book as a thank you. Bill’s next MrExcel webcast, “PowerExcel” is Tuesday, June 14, at 2:00 Eastern Time. Click here for more details as they become available.