The Power of AutoFilters and Subtotals

AutoFilter is a powerful feature where data can be filtered anyway you want. Subtotal works in conjunction with AutoFilter to display totals only in the filtered data. For example, if you wanted to know the total cost of Product A in the Northwest Region of the company, you can do it with AutoFilter and Subtotal. To get started, let's work first with AutoFilter and a simple spreadsheet.

Enter the data as shown in Figure 1.1.

Figure 1.1


Highlight the area as shown in Figure 1.2.

Figure 1.2

Now click on the menu Data>Filter>AutoFilter. Your data should now have an AutoFilter like Figure 1.3.

Figure 1.3

Now that you have AutoFilter, you can filter the data anyway you like by clicking on the according drop down arrow. For example, if you wanted to only see the costs for Product A in the NorthWest Region, select Product A from it's drop down and select Northwest from it's drop down. The file should now look like Figure 1.4.

Figure 1.4

You will notice that you only see rows 1 to 4 visibile. You will also notice that the row numbers changed to the colour blue to tell you the user that there is at least one filter on. To turn off all filters go to the menu Data>Filter>Show All. This will remove all filters. The first row that is not part of the filter is row 12. This is very important for Subtotals. If you enter data into row 12 then the Autofilter will view row 12 as a part of the filter and you will not see the subtotal.

Therefore , enter the following formula in row 13 column C:

=subtotal(9, c2:c11)

where 9 is the subtotal function of SUM and c2:c11 is the range to calculate the subtotal.

You can change the function of the subtotal by changing the 9 number to:

1 = AVERAGE OF THE RANGE

2 = COUNT ONLY NUMBERS OF THE RANGE

3 = COUNTA (COUNT ALL IN THE RANGE THAT IS NOT BLANK)

4 = MAX OF THE RANGE

5 = MIN OF THE RANGE

6 = PRODUCT OF THE RANGE

7 = STANDARD DEVIATION OF THE RANGE

8 = STDEVP OF THE RANGE (I DON'T USE THIS BUT SOMEBODY MUST)

9 = SUM OF THE RANGE

10 = VAR OF THE RANGE (I DON'T USE THIS EITHER)

11 = VARP OF THE RANGE (I DON'T USE THIS)

Your spreadsheet should now look like Figure 1.5.

Figure 1.5

I'm not going to show you formatting because that stuff is quite easy. I do recommend to Show All data so nothing is filtered and then start formatting. If you don't, then only the filtered rows and columns will be formatted to your liking. Remove gridlines(see lesson on that in my archives) and play with the borders, colors and alignment. In the end Figure 1.5 will turn into this:

Figure 1.6

To download the completed file click here.

That's it. Keep Excelling! More tutorials and tips to come.