How do I create a dynamic PivotTable filter in Excel?

You can create a dynamic PivotTable filter by adding an ActiveX text box, linking it to a worksheet cell, and using a short VBA event macro to show only the PivotItems that match the typed text. In this example, the text box is called TextBox1, the linked cell is E1, the PivotTable is called PivotTable1, and the field being searched is Product Name.

How the dynamic PivotTable filter works

The macro reads the value typed into a search cell, checks whether any PivotItems match, and only then changes the visible PivotItems. That first check matters because Excel will throw an error if your code attempts to hide every item in a PivotField.

Before you use the code

The example workbook uses Sheet1 for the source data table called Table1.
Sheet2 contains the PivotTable and the ActiveX search box.
The workbook must be saved as a macro-enabled .xlsm file so the VBA code is kept.

Create a live search box for a PivotTable in Excel

Create the source table

Put your data on Sheet1, click inside the data and press Ctrl + T.
- Make sure My table has headers is ticked.
- Go to Table Design and name the table Table1.
Create the PivotTable

Click inside Table1, then go to Insert > PivotTable and place it on a new sheet.
- Add Product Name to the PivotTable.
- Click inside the PivotTable, go to PivotTable Analyse and set the PivotTable name to PivotTable1.
Enable ActiveX controls

Go to File > Options, choose Trust Centre, then click Trust Centre Settings.
- Go to ActiveX Settings.
- Choose Enable all controls without restrictions and without prompting.
Only enable ActiveX controls for files you trust.
Show the Developer tab

Go to File > Options, choose Customise Ribbon, tick Developer and click OK.
Insert the ActiveX text box

Go to Developer > Insert, choose Text Box under ActiveX Controls, and draw the text box above the PivotTable.
- Turn Design Mode on.
- Right-click the text box and choose Properties.
- Set Name to TextBox1.
- Set LinkedCell to E1. You can hide column E later.
Add the VBA code

Go to Developer > Visual Basic. In the left panel, double-click the sheet that contains the PivotTable, then paste the code below into that sheet module.
Test the search box

Close the Visual Basic Editor, turn Design Mode off, click inside the text box and type part of a product name. The PivotTable should filter as you type.
Save the file

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). Do not save it as .xlsx, because that will remove the VBA code.

VBA code for the dynamic filter

Private Sub TextBox1_Change()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim pattern As String
    Dim matchFound As Boolean

    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Product Name")

    pattern = "*" & LCase(ActiveSheet.Range("E1").Value) & "*"

    If ActiveSheet.Range("E1").Value = "" Then
        pf.ClearAllFilters
        Exit Sub
    End If

    matchFound = False

    For Each pi In pf.PivotItems
        If LCase(pi.Name) Like pattern Then
            matchFound = True
            Exit For
        End If
    Next pi

    If Not matchFound Then Exit Sub

    For Each pi In pf.PivotItems
        pi.Visible = LCase(pi.Name) Like pattern
    Next pi

End Sub

When this is useful

This approach is handy when delegates need a search-style filter for long product, customer, department or account lists inside a PivotTable. It is more controlled than manually ticking items in the filter dropdown and can make a dashboard easier to use.

Dynamic PivotTable filter FAQs

Can you make a PivotTable filter update as you type?

Yes. The usual method is to use an ActiveX text box with a TextBox1_Change event. Each time the text changes, VBA checks the PivotItems in the chosen PivotField and hides the items that do not match.

Why does Excel give an error when filtering PivotItems with VBA?

Excel will not allow every item in a PivotField to be hidden. The code on this page checks that at least one PivotItem matches the search text before changing item visibility, which avoids that common error.

Do I have to use an ActiveX text box?

No. ActiveX is convenient because the code can run as soon as the text changes, but you could adapt the same idea to read from a normal worksheet cell and trigger the macro another way.

Why is the text box linked to cell E1?

The linked cell gives the VBA code a simple place to read the search term from. You can use another cell, but you must update the reference in the code. You can hide the linked cell column after setup if you do not want users to see it.

Will this work in an .xlsx workbook?

No. The workbook must be saved as an Excel Macro-Enabled Workbook (*.xlsm). Saving as .xlsx removes the VBA code.

Pivot Table Dynamic Filter with VBA