Top 10 Filters are a quick and easy way to focus on key items in a pivot table. Instead of looking at all the data, hide everything except a set number of top or bottom items.
In the screen shot below, you can see the Pivot Table Top 10 Filter dialog box. There are four drop down lists, where you can change the settings, but Excel won’t let you link to a worksheet cell, like it does in some dialog boxes.
Select Top 10 Settings on the Worksheet
Fortunately, you can use a bit of Excel programming to update a Top 10 filter, based on the values entered on a worksheet.
In this example, two cells have been set up with drop down lists, and selections in those cells will be used in an automated Top 10 filter.
In cell E1, select either Top or Bottom, to focus on the best or worst items.
Then, in cell E2, select the number of items that you want to see in the filtered list. The pivot table filter will automatically update when you change either of the yellow cells.
Download the Sample File
To get the pivot table top 10 filter VBA code, and to download the sample file, go to the Pivot Table Filters – Top 10 page on my Contextures website.
The sample file is in xlsm format, and contains macros, so be sure to enable macros if you want to test the code.