Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In Excel 2013, you can also use slicers on formatted Excel tables.
You can use programming to control the slicers, and this page has sample code that you can use. When you select an item in one slicer, it will automatically select the same item in another slicer.
If multiple pivot tables are based on the same data source, you can create a slicer, and connect it to one or more of those pivot tables. However, if there are pivot tables from different data sources, you can't connect the to the same slicer.
In this example, there is a pivot table based on a table with sales data, and a slicer for the City field.
The source data has four cities, and we only want Boston and Los Angeles available in the slicer The slicer shows all the items, and we can't hide the cities that we don't want to use.
Instead, we'll create another pivot table, with just the short list of cities, and use programming to change the selections in the main slicer.
Follow these steps to set up the short list of cities.
If you are using Excel 2013, you can use a slicer on an Excel table. In Excel 2010 and 2007, you'll have to build a pivot table, based on the city list, before you can create the slicer.
Follow these steps to build a pivot table.
The next step is to add a slicer. In Excel 2013, you can connect the slicer directly to the table, and in Excel 2007 or 2010, use the pivot table.
Follow these steps to add the slicer:
The next step is to add code to the worksheet module, for the sheet where the short city list is stored.
Private Sub Worksheet_PivotTableUpdate _ (ByVal Target As PivotTable) Dim wb As Workbook Dim scShort As SlicerCache Dim scLong As SlicerCache Dim siShort As SlicerItem Dim siLong As SlicerItem On Error GoTo errHandler Application.ScreenUpdating = False Application.EnableEvents = False Set wb = ThisWorkbook Set scShort = wb.SlicerCaches("Slicer_City") Set scLong = wb.SlicerCaches("Slicer_City1") scLong.ClearManualFilter For Each siLong In scLong.VisibleSlicerItems Set siLong = scLong.SlicerItems(siLong.Name) Set siShort = Nothing On Error Resume Next Set siShort = scShort.SlicerItems(siLong.Name) On Error GoTo errHandler If Not siShort Is Nothing Then If siShort.Selected = True Then siLong.Selected = True ElseIf siShort.Selected = False Then siLong.Selected = False End If Else siLong.Selected = False End If Next siLong exitHandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub errHandler: MsgBox "Could not update pivot table" Resume exitHandler End Sub
The final step is to move the slicers -- the short list slicer will go onto the main sheet, beside the original pivot table. The long slicer will go onto the short list sheet, where it will be updated by the code.
To move a slicer:
After moving both the slicers, click on a city in the short city slicer, and the main pivot table should change, to show only the results for that city. If you check the slicer on the short list sheet, it should have changed, to show the same city (or cities) selected.
1. To download the completed sample file, click here. The zipped file contains macros, so enable macros to test the slicers.
2. Download a completed sample file that has two sets of Slicers (one for City, one for Category). The zipped file contains macros, so enable macros to test the slicers.
Last updated: September 28, 2016 7:22 PM