How to filter Pivot table based on a specific cell value in Excel?
Normally, we are filtering data in a Pivot Table by selecting items in the drop-down list as shown in the screenshot below. Actually, you can filter a pivot table based on the value in a specific cell. The VBA method in this article will help you solve the problem.
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,
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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!