Excel Pivot Table Filters - Top 10

Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum.

Video: Using Pivot Table Top 10 Filters

Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum.

The written instructions are below the video. 

Using Excel Pivot Table Top 10 Filters 

You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.

For example, insteading of showing the total sales for all products, use a filter to show just the top 10 products, or narrow it down to the top 2.

In the screen shot below, the City field has been filtered to show only the top 2 cities, with the highest sales amounts.

Or, if you want to focus on the poor performers, you can use a value filter to find the bottom 5 products or sales regions.

Filter a Pivot Table for Top 10 Items 

In the example shown below, there are 24 months of Order dates in the Row Labels area. In the Values area, you can see the total sales for the first few order dates.

To filter the pivot table, so it shows only the Top 10 order dates, follow these steps.

1. In the Pivot Table, click the drop down arrow in the OrderDate field heading.
2. In the pop-up menu, click Value Filters, then click Top 10.



3. In the Top 10 Filter dialog box, leave the default settings as is (Top 10 Items Sales):



4. Click OK, to close the Top 10 Filter dialog box, and apply the Value Filter.

As shown in the filtered example below, only the top 10 Order Dates are now visible, still sorted by order date.

1. To sort the amounts in descending order, right-click on one of the amounts, and click Sort.
2. Then click Sort Largest to Smallest.

Modify a Top 10 Filter 

After you add a Top 10 Filter, you can change it, to show a different result.

1. In the Pivot Table, click the drop down arrow in the OrderDate field heading.
2. In the pop-up menu, click Value Filters, then click Top 10.
3. In the Top 10 Filter dialog box, change the number of Items to 5.
4. Click OK, to close the Top 10 Filter dialog box, and apply the Value Filter.

The results change, to show only the 5 order dates with the highest sales amounts.

Filter a Pivot Table for Bottom 10 Items 

The Top 10 filter helps you find the highest amounts, but sometimes you need to find the lowest amounts, to focus on those. Even though the filter is named "Top 10", you can use it to find the bottom amounts too.

To see only the Bottom 10 order dates, follow these steps.

1. In the Pivot Table, click the drop down arrow in the OrderDate field heading.
2. In the pop-up menu, click Value Filters, then click Top 10.
3. In the Top 10 Filter dialog box, select Bottom from the first drop down.
   
   
   
   
4. Click OK, to close the Top 10 Filter dialog box, and apply the Value Filter.

The results change, to show only the 5 order dates with the lowest sales amounts.

Clear the Field Filters 

When you've finished analyzing the filtered data, you can clear the Filters, to see all the data again.

1. In the pivot table, click the drop down arrow in the OrderDate field heading.
2. Click Clear Filter From OrderDate Field, to remove the filter criteria, and show all the data.

Filter a Pivot Table for Top 10 Percent 

In addition to filtering for the top or bottom items, you can use a Value Filter to show a specific portion of the grand total amount. In the screenshot below, you can see that the grand total sales amount is 663,732.

Ten percent of the grand total is 66,373, and you can use a Top 10 filter to find the top or bottom dates combine to total at least that amount.

To see only the top selling order dates that contribute to 10% of the total sales amount, follow these steps.

1. In the Pivot Table, click the drop down arrow in the OrderDate field heading.
2. In the pop-up menu, click Value Filters, then click Top 10.
3. In the Top 10 Filter dialog box, change the settings to:
   Top 10 Percent Sales.

The results change, to show only the top 2 order dates, because their combined sales are greater than 10% of the original grand total amount.

Filter a Pivot Table for the Bottom Sum 

Another way to use the Top 10 Value Filter is to find the items that make up a specific sum. For example, from the order dates with the lowest sales amounts, which order dates would combine to total at least 100,000 in sales.

To see only the bottom selling order dates that contribute to 100,000 of the total sales, follow these steps.

1. In the Pivot Table, click the drop down arrow in the OrderDate field heading.
2. In the pop-up menu, click Value Filters, then click Top 10.
3. In the Top 10 Filter dialog box, change the settings to:
   Bottom 100000 Sum Sales.

The results change, to show only the 6 lowest order dates, because their combined sales are at least 100,000.

As you can see in the pivot table shown below, the bottom 5 order dates only total 98,165, so the 6th lowest order date is also included in the Value Filter results, achieve our 100,000 minimum.

Top 10 Filter - Select on Worksheet (Macro)

The selection boxes in the Top 10 Filter dialog cannot be linked to worksheet cells, to get a value.

However, you can use an Excel macro to get the values that have been entered on a worksheet. In the screen shot below, when you select from the drop down lists in the yellow cells, a Top Ten filter is applies to the OrderMth field, with the settings from cells E1 and E2.

This example is in the download file, on the Top10Filter sheet. Read the sections below, for details on how the workbook is set up, and to see the code that runs the filter.

Set up the Drop Down Lists

On the Lists worksheet, there is a list of numbers, named NumList, in cells A2:A10. In cells C2:C3, there is a list of filter types, named TypeList

.

On the Top10Filter sheet, the yellow cells (E1:E2) contain data validation drop down lists, based on those lists.

Cell E1 is named TypeSel, and cell E2 is named NumSel.

Get the Selected Type Number

To use the selected filter type in the macro, it will be changed to a number.

On the Lists worksheet, to the right of the TypeList, a number has been entered for each filter type.

Then, in a cell named TypeValSel (D5), a VLOOKUP formula returns the number for the Type selected in cell E1 (TypeSel), on the Top10Filter sheet.

How It Works

If you record a macro while applying a Top 10 filter to a pivot table, you would see either xlTopCount or xlBottomCount in the recorded code. Those are constants for the Type argument when adding a pivot table filter.

It will be easier to send the "Top" or "Bottom" selection to our macro, if we use the numeric values of those constants.

To get the numeric values for a constant, type a question mark in the Immediate window, followed by the constant, and then press Enter

.

The Top 10 Filter Code

To filter the pivot table automatically, when a value is selected from the drop down lists, a Worksheet_Change event is added to the Top10Filter sheet code module.

Right-click the Top10Filter sheet tab, and click View Code. The following code is pasted onto that code module. See below, for an explanation of how the code works

NOTE: There is also a version of the macro for multiple pivot tables on the same sheet, based on the same data source.

Private Sub Worksheet_Change _
  (ByVal Target As Range)
Dim ws As Worksheet
Dim wsL As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pfD As PivotField
Dim rngType As Range
Dim rngTypeSel As Range
Dim rngNum As Range
Dim lType As Long

On Error GoTo errHandler

Set ws = ActiveSheet
Set wsL = Worksheets("Lists")
Set pt = ws.PivotTables(1)
Set pf = pt.PivotFields("OrderMth")
Set pfD = pt.DataFields(1)
Set rngType = ws.Range("TypeSel")
Set rngTypeSel _
  = wsL.Range("TypeValSel")
Set rngNum = ws.Range("NumSel")

Select Case Target.Address
  Case rngType.Address, _
    rngNum.Address
  Application.ScreenUpdating = False
  pf.ClearAllFilters
  If rngNum.Value > 0 And _
        rngType > 0 Then
    pf.PivotFilters.Add _
        Type:=rngTypeSel.Value, _
        DataField:=pfD, _
        Value1:=rngNum.Value
  End If
End Select

exitHandler:
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  MsgBox "Could not apply filter"
  Resume exitHandler
End Sub

How the Code Works

Variables are defined for the sheets, pivot table, and pivot fields, as well as the ranges that contain values for the filter. For example:

Set rngType = ws.Range("TypeSel")

When a change is made on the worksheet, the Worksheet_Change code is triggered. It checks which cell has been changed (Target), and compares that cell's address to the addresses of the TypeSel and NumSel ranges.

Select Case Target.Address
  Case rngType.Address, _
    rngNum.Address

If one of those cells is a match for the Target address, the filter code runs. If any other cell was changed, the filter code does not run.

First, the screen updating is turned off, to prevent the macro from running slowly.

Application.ScreenUpdating = False

Then, all filters are cleared from the OrderMth field.

pf.ClearAllFilters

The NumSel and TypeValSel ranges are checked, to see if they have a value higher than zero,

  If rngNum.Value > 0 And _
        rngType > 0 Then

If both are higher than zero, the Top 10 Filter is applied, using the type number and number of items from the yellow cells.

pf.PivotFilters.Add _
        Type:=rngTypeSel.Value, _
        DataField:=pfD, _
        Value1:=rngNum.Value

Finally, the screen updating is turned on, to allow Excel to display the changes.

Application.ScreenUpdating = True

Top 10 Filter Code - Multiple Pivot Tables

If there are multiple pivot tables on the same sheet, based on the same data source, use the following code to filter all of them. All pivot tables must have the designated Row Field and Value Field, but can have other column or data fields too.

Private Sub Worksheet_Change _
  (ByVal Target As Range)
Dim ws As Worksheet
Dim wsL As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pfD As PivotField
Dim rngType As Range
Dim rngTypeSel As Range
Dim rngNum As Range
Dim lType As Long
Dim strPF As String
Dim strDF As String
strPF = "OrderMth"
strDF = "Sum of Quantity"

On Error GoTo errHandler

Set ws = ActiveSheet
Set wsL = Worksheets("Lists")
Set rngType = ws.Range("TypeSel")
Set rngTypeSel _
  = wsL.Range("TypeValSel")
Set rngNum = ws.Range("NumSel")

Select Case Target.Address
  Case rngType.Address, _
    rngNum.Address
      Application.ScreenUpdating = False
      Application.EnableEvents = False
For Each pt In ws.PivotTables
  Set pf = pt.PivotFields(strPF)
  Set pfD = pt.DataFields(strDF)
  pf.ClearAllFilters
  If rngNum.Value > 0 And _
        rngType > 0 Then
    pf.PivotFilters.Add _
        Type:=rngTypeSel.Value, _
        DataField:=pfD, _
        Value1:=rngNum.Value
  End If
Next pt

End Select

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

errHandler:
  MsgBox "Could not apply filter"
  Resume exitHandler
End Sub

Download the Sample File

To experiment with the filters, you can download the sample file. The file is in xlsm format, and contains macros.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

More Pivot Table Resources

• Pivot Table Blog
• Pivot Table Article Index
• Pivot Table Video Index

• Add-In: PivotPower Premium
• Beginning Pivot Tables Book
• Pivot Tables Recipe Book

Tutorials:

• FAQs - Pivot Tables
• Pivot Table Introduction
• Grouping Data
• Multiple Consolidation Ranges
• Running Totals
• Summary Functions
• Clear Old Items in Pivot Table