MrExcel.com - Your One Stop for Excel Tips & Solutions

Build a Better Top Five with A Filter Hack

January 02, 2023 - by

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.

The Filter command on the Data tab is greyed out if you are in a pivot table. But if you choose one of the blank cells immediately to the right of the pivot table, you can click the Filter and the pivot table headings will have filter icons. This is a loophole or bug in Excel.
Figure 910. 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.

From the Sum of Revenue drop-down in B3, choose Number Filters, Top 10...
Figure 911. 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.

To get a report of the top five items set the Top 10 AutoFilter to display the Top SIX items. Why is it one more than you want? because the Grand Total is the largest item to this filter.
Figure 912. 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 909, except this figure has the correct total for the entire data set.

The report now shows the top 5 customers and the total of all customers - $6.7 Million
Figure 913. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tushar Rathour on Unsplash