Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In Excel 2013 and later versions, you can also use slicers on formatted Excel tables.
You can use programming to control the Excel 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.
Update Multiple Pivot Table Slicers
In an Excel workbook, some pivot tables might be based on the same pivotcache, and other pivot tables are based on different pivot caches.
In the steps below, see how to set up a workaround, to automatically update slicers for pivot tables that have different data sources, when one of those slicers changes.
-- 1) Workbook Setup
-- 3) Build a Pivot Table
-- 4) Add a Slicer
-- 5) Add Worksheet Code
-- 6) Move the Slicers
NOTE: You can download a copy of this example, in the Download section at the bottom of this page
In this workbook example:
In the sales data used for the pivot table, there are sales records from four different cities: Boston, Los Angeles, New York, and San Diego.
As a way to show specific cities only, we'll use this workaround:
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:
Get Slicer Name for Macro
After the Slicer is added to the worksheet, you can change its settings, if needed, and test the Slicer.
To see the slicer's "formula" name, so you can add that to the macro, follow these steps:
Test the Slicer
Next, to test the slicer, follow these steps:
The next step is to add Excel VBA 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.
Update Multiple Excel Table Slicers
In the Download section, there is another Slicer Macro workbook that you can download, and it has two different Excel tables with sales data.
Two Excel Tables and Slicers
In the workbook, each Excel table is on a separate sheet, with a Region Slicer at the top left corner of the sheet.
Admin Sheet with Macro Code
On another sheet, named Admin, there is a Slicer for each sales data table, showing the Region names.
On the Admin, there is also a small pivot table for each sales data table. There is only one field in the layout for each pivot table - the Region field, in the Row area.
Note: The Slicers at the top left of the Sales Data sheets are connected to those small pivot tables.
Admin Sheet Code
On the Admin sheet code module, there is code that runs automatically, when one of those small pivot tables is updated -- for example, if you click a Slicer to select a Region name.
Here is the code -- it gets the name of the pivot table that was updated, then runs a macro named UpdateSlicers. You can see that code in the next section, and in the sample file.
Private Sub Worksheet_PivotTableUpdate _ (ByVal Target As PivotTable) strPT = Target.Name UpdateSlicers End Sub
Macro Code to Update Slicers
Here is the code for the UpdateSlicers macro, which is stored on a regular code module, named modSlicers, in the sample file.
Option Explicit Global strPT As String '---------------------- Sub UpdateSlicers() Dim wb As Workbook Dim pt As PivotTable Dim scMain As SlicerCache Dim sc As SlicerCache Dim siMain As SlicerItem Dim si As SlicerItem On Error GoTo errHandler Application.ScreenUpdating = False Application.EnableEvents = False Set wb = ThisWorkbook Set pt = Sheets("Admin").PivotTables(strPT) Set scMain = pt.Slicers(1).SlicerCache For Each sc In wb.SlicerCaches If sc.Name <> scMain.Name Then sc.ClearManualFilter End If For Each si In sc.VisibleSlicerItems Set si = sc.SlicerItems(si.Name) Set siMain = Nothing On Error Resume Next Set siMain = scMain.SlicerItems(si.Name) On Error GoTo errHandler If Not siMain Is Nothing Then If siMain.Selected = True Then si.Selected = True ElseIf siMain.Selected = False Then si.Selected = False End If Else si.Selected = False End If Next si Next sc exitHandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub errHandler: MsgBox "Could not filter all tables" Resume exitHandler End Sub
Video: Connect Slicer to Multiple Pivot Tables
In this video, you'll see the steps for connecting multiple pivot tables to a slicer, so they can all be filtered with a single click.
Use this technique, if all pivot tables are based on the same data source.
The written instructions are on the Pivot Table Slicers page.
Last updated: March 3, 2023 10:45 AM