How to filter Pivot table based on a specific cell value in Excel?
Normally, we are filtering data in a Pivot Table by checking values from the drop-down list as the left screenshot shown. If you want to make a Pivot Table more dynamic by filtering, you can try to filter it based on value in a specific cell. The VBA method in this article will help you solving the problem.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
The following VBA code can help you filtering a Pivot Table based on a specific cell value in Excel. Please do as follows.
1. Please enter a value you will filter Pivot Table based on into a cell in advance (here I select cell H6).
2. Open the worksheet contains the Pivot Table you will filter by cell value. Then right click the sheet tab and select View Code from the context menu. See screenshot:
3. In the opening Microsoft Visual Basic for Applications window, copy below VBA code into the Code window.
VBA code: Filter Pivot Table based on cell value
Private Sub Worksheet_Change(ByVal Target As Range) 'Update by Extendoffice 20180702 Dim xPTable As PivotTable Dim xPFile As PivotField Dim xStr As String On Error Resume Next If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub Application.ScreenUpdating = False Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2") Set xPFile = xPTable.PivotFields("Category") xStr = Target.Text xPFile.ClearAllFilters xPFile.CurrentPage = xStr Application.ScreenUpdating = True End Sub
Notes: In the code,
1) “Sheet1” is the name of the worksheet.
2) “PivotTable2” is the name of the Pivot Table.
3) The filtering field in the pivot table is called "Category".
4) The value you want to filter the pivot table is placed in cell H6.
You can change the above variable values as you need.v
4. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
Then the Pivot Table is filtering based on the value in cell H6 as below screenshot shown:
You can change the cell value to others as you need.
Note: Values you type in cell H6 should exactly match the values in the Category drop down list of Pivot Table.
- How to combine multiple sheets into a pivot table in Excel?
- How to create a Pivot Table from Text file in Excel?
- How to link Pivot Table filter to a certain cell in Excel?
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 8 months agoHow would you do this with a report filter that has a hierarchy?
To post as a guest, your comment is unpublished.· 9 months agoUsing this code (updated for my variables of course), when changing the field, the filter changes momentarily to the correct one, and then clears itself almost immediately. Trying to figure out why it's doing this (wondering if it has something to do with the ClearAllFilters at the end of the sub?)