How to disable select multiple items in a pivot table filter

Question

I have built a pivot table with filters, but I would like for users to only be able to select one item within a filter at a time to limit the results in the table. I do not wish to use a slicer.

Answer 1

Hi, Eryn Wright

There is no regular PivotTable setting that permanently locks the filter to one item. Leaving Select Multiple Items unchecked only affects the current selection, so another user can enable it again.

For desktop Excel, the practical solution is a small VBA macro. Press Alt+F11, select Insert > Module, then paste:

Sub DisableMultipleSelection()
    Dim pf As PivotField
    With ActiveSheet.PivotTables("PivotTable1")
        For Each pf In .PageFields
            pf.EnableMultiplePageItems = False
        Next pf
    End With
End Sub

Replace PivotTable1 with the actual PivotTable name, then run the macro and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

The EnableMultiplePageItems property controls whether checkboxes appear in report-filter dropdowns. Setting it to False removes the Select Multiple Items option, so each report filter allows only one item at a time.

This applies to fields placed in the PivotTable’s Filters area. Users must open the file in desktop Excel with macros allowed. VBA will not run in Excel for the web.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.