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. You don't need complex formulas - use built-in filters!

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. 

Video: Compare Top and Bottom Product Sales

In this video, a pivot table summarizes the products sales over a two year period. With a Top 10 Filter, you can quickly show the top products, and compare top and bottom product sales.

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, instead of showing the total sales for all products, use this type of filtering to show just the top 10 products, or narrow it down to the top 2.

In the screen shot below, the pivot table field City is in the Row area. City has been filtered to show only the top 2 cities, with the highest sales amounts.

Tip: 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, use the following 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 screen shot 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.

Macro: Top 10 Filter - Worksheet

In the Top 10 Filter dialog, you have to type a number in the second box. Unfortunately, you can't click on a worksheet cell, to force the filter to use a cell's value.

Top 10 Filter Macro

However, you can use the Excel macro, shown below, to get values from a worksheet, and use those in the Top 10 filter. This macro is in the download file, on the Top10Macro sheet's code module. (Right-click that sheet tab, and click View Code)

• In the sample file, select a Filter Type from the drop down list in cell F1
• Then, type a number in cell F2, and press Enter

The macro runs automatically, to apply a Top 10 filter with the selected settings.

Set up Drop Down List

On the Lists worksheet, there is a list of Top 10 filter types, named TypeList

On the Top10Filter sheet, cell F1 has a data validation drop down list, based on the TypeList items

• Cell F1 is named TypeSel
• Cell F2 is named NumSel

Those names are used in the macro, to check if a filter setting cell has been changed

Get the Selected Type Number

In the list of filter types, each type has a number in the next column. .

In a cell named TypeValSel (E5), a VLOOKUP formula returns the number for the Filter Type selected (TypeSel) on the Top10Macro sheet.

How It Works

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

It will be easier to send Filter Type selection to our macro, if we use the numeric values of those constants.

TIP: To see 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, there is a Worksheet_Change event on the Top10Filter sheet code module. See below, for an explanation of how the code works

• To see the code in the sample file, right-click the Top10Filter sheet tab, and click View Code.

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.RowFields(1)
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.EnableEvents = False
    Application.ScreenUpdating = False
    pf.ClearAllFilters
    If rngNum.Value > 0 And _
          rngTypeSel > 0 Then
      pf.PivotFilters.Add _
          Type:=rngTypeSel.Value, _
          DataField:=pfD, _
          Value1:=rngNum.Value
    End If
End Select

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = 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 _
        rngTypeSel > 0 Then

If both are higher than zero, the Top 10 Filter is applied, using the type number and number of items from the filter setting 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.

NOTE: In each pivot table, the Top Ten filter will be applied:

• to the first Row field
• based on the numbers in the first Values field

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 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.RowFields(1)
  Set pfD = pt.DataFields(1)
  pf.ClearAllFilters
  If rngNum.Value > 0 And _
        rngTypeSel > 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

Get the Sample File

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

More Tutorials

FAQs - Pivot Tables

Pivot Field Multiple Filters

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table