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!
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.
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.
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.
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:
As shown in the filtered example below, only the top 10 Order Dates are now visible, still sorted by order date.
After you add a Top 10 Filter, you can change it, to show a different result.
The results change, to show only the 5 order dates with the highest
sales amounts.
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.
The results change, to show only the 5 order dates with the lowest
sales amounts.
When you've finished analyzing the filtered data, you can clear the Filters, to see all the data again.
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.
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.
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.
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.
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.
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)
The macro runs automatically, to apply a Top 10 filter with the selected settings.
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
Those names are used in the macro, to check if a filter setting cell has been changed
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.
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
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
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
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
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:
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
To experiment with the filters, you can download the sample file. The file is in xlsm format, and contains macros.
Clear Old Items in Pivot Table
Last updated: February 1, 2022 2:45 PM