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.
_____________________
Hi Debra,
Why are you adding COUNT column to the dataset and using it in pivot tables?
I know you know that we can get the same results without that in the sample workbook. So what is the reason? Just to be safe in case of blanks in original data?
I am dying of curiosity 🙂
@pmsocho Thanks for asking about that Count field. You’re right — we can use the COUNT function on any field, to show a count of records.
However, if you try to use that number in a calculated field, the SUM is always used, even if it’s displayed as COUNT.
I wrote about it here: http://www.contextures.com/excelpivottablecalculatedfieldcount.html
I’ve been using Excel and PT for years but I had never faced that problem. Good to know! Thanks for answering.
Good to view solution of this type of problem. Thanks for sharing it. Since long, I have been working on excel and powerpoint.