Pivot Table Top 10 Filter From Worksheet Values

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.

Pivot Table Top 10 Filter dialog http://www.pivot-table.com/

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.

Pivot Table Top 10 Filter Macro http://www.pivot-table.com/

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.

top10filtervba01

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.

Pivot Table Top 10 Filter Macro http://www.pivot-table.com/

_____________________

This entry was posted in Sort and Filter. Bookmark the permalink.

4 Responses to Pivot Table Top 10 Filter From Worksheet Values

  1. pmsocho says:

    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 🙂

  2. Neila says:

    Good to view solution of this type of problem. Thanks for sharing it. Since long, I have been working on excel and powerpoint.

Leave a Reply

Your email address will not be published. Required fields are marked *