This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Build a Better Top Five with A Filter Hack
Problem: Can you AutoFilter a pivot table? If you could turn on the AutoFilters and then filter to the top 6 items in column B, you would get the top five customers plus the real total.
Unfortunately, AutoFilter is greyed out when you are in a pivot table.
Strategy: Filter from the magic cell.
There is a strange loophole in the Filter logic. There are four ways to filter a data set. Microsoft greys out the Filter icon in the Data tab for three of those ways. They apparently missed the fourth method.
1. Create a pivot table with customers in the row labels.
2. Sort high to low by revenue.
3. Select the cell to the right of the last heading. Below, this is cell C3. I call this cell the “magic cell" because it is an arcane cell that can filter the adjacent data set. It is so arcane, Microsoft forgets to gray out the filter command.
4. On the Data tab, click Filter.
Figure 848 Filter is supposed to be greyed out for pivot tables.
5. Open the dropdown in B3. You now have the AutoFilter choices instead of the Pivot filter choices. Choose Number Filters, Top 10.
Figure 849 These aren’t the usual pivot filters.
6. If you want to see the top five customers, choose 6 from the Top 10 AutoFilter dialog.
Figure 850 Ask for the Top 6 customers.
Result: the AutoFilter will show the largest item which is the Grand Total, plus the next five largest items, which are the customer totals. This figure is very similar to Figure 843, except this figure has the correct total for the entire data set.
Figure 851 Top five customers, with total of all customers.
Gotcha: Don’t forget the magic cell. In order to turn off the AutoFilter dropdowns, you have to go back and select cell C3. Otherwise, the Filter icon is greyed out.
Gotcha: This trick is clearly exploiting a bug in Excel. Don’t expect the AutoFilter to recalculate if you refresh the pivot table. Microsoft never expected that anyone would be able to AutoFilter a pivot table.