When setting up an Excel tool, there are instances where you want the user to be able to update the filter of a pivot table that is situated at the back-end of the Excel workbook, without having to navigate to the raw data section of the document. While it is possible to use pivot slicers for this, slicers tend to take up space and may not offer certain functionalities that you may want. With the help of a combo box, an INDEX formula and a short VBA macro, it is possible to update pivot table filters without interacting directly with the pivot table itself.

Step 1: Insert a new combo box on your Excel sheet (File>Developer>Controls>Insert>ComboBox). Set up the combo index to return to a cell on the raw data sheet.

Step 2: In another cell on the same sheet, enter an =INDEX() formula to fetch the pivot table’s filter items based on the combo box index return. Give this cell a name for reference purposes (selDivname)

Step 3: Give the cell of the pivot table filter a name (PvtDvsn_filter)

Step 4: Open up a new VBA module (use keyboard shortcut Alt+F11 to bring up the Visual Basic Editor, then Insert>Module) and place the code below. Adjust the sections in blue to match your own range names.

Sub DropDown_Change()
Application.ScreenUpdating = False
[PvtDvsn_filter].Value = [selDivname].Value
Application.ScreenUpdating = True
End Sub

Step 5: Right-click on the created combo box, select “Assign macro” and assign the DropDown_change macro to the combo box.

Step 6: Save your workbook as a macro-enabled worksheet and click on the drop down of the combo box to update your pivot table filter.