Home > Macros > Basics > Slicers

Update Excel Slicers with Macro

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.

• If multiple pivot tables are based on the same data source (pivot cache), you can create a slicer, and connect it to one or more of those pivot tables.
  • See the steps in the Pivot Table Slicer video, further down on this page
• However, if there are pivot tables from different data sources (pivot caches), you CANNOT connect them to the same slicer.

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

-- 2) Create Short List of Cities

-- 3) Build a Pivot Table

-- 4) Add a Slicer

-- 5) Add Worksheet Code

-- 6) Move the Slicers

1) Workbook Setup

NOTE: You can download a copy of this example, in the Download section at the bottom of this page

In this workbook example:

• There is a pivot table based on a table with sales data
• That pivot table has a slicer for the City field.

Slicer Requirements

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.

• The Slicer automatically shows all four cities
• However, when people are using the pivot table report, we only want Boston and Los Angeles shown on the Slicer
• Unfortunately, there's no setting in the Slicer, where we can hide specific items from the pivot field

As a way to show specific cities only, we'll use this workaround:

• Create a short list of cities -- just the ones we want in the Slicer
• From that list, create another pivot table, with just the short list of cities
• Add programming code, that will change the selections in the main slicer (all city names), when the slicer with the short city list is used, even though the two Slicers have different data sources.

2) Create Short List of Cities

Follow these steps to set up the short list of cities.

1. On a blank worksheet, type the heading, City, and a list with the two cities, Boston and Los Angeles.
2. Select a cell in the list, and on the Home tab of the Ribbon, click Format as Table
3. Click on a Table Style
4. Check the data range, and add a check mark to "My table has headers"
5. Click OK, to confirm that you want to create a table.

3) Build a Pivot Table

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.

1. Select a cell in the short list of cities.
2. On the Insert tab of the Ribbon, click PivotTable
3. Select "Existing worksheet" and click in the Location box
4. Select a cell on the same worksheet as the list, and click OK

   

5. In the pivot table field list, add a check mark to City, to add it to the pivot table, in the Row area

   

4) Add a Slicer

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:

• Select a cell in the pivot table (2007/2010) or the table (2013).
• On the Excel Ribbon, go to the Insert tab
• In the Filter group, click the Slicer command
• In the Insert Slicers dialog box, add a check mark to City
• Then, click OK, to insert Slicer on the worksheet

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:

• First, click on an empty part of the Slicer, to select it
• Next, on the Excel Ribbon, click the Slicer tab -- it only appears when a Slicer is selected
• At the left end of the Slicer tab, click the Slicer Settings command
• The Slicer Settings dialogue box opens, and the Slicer name is shown near the top, as Name to use in formulas -- Slicer_City1, in the screen shot below.
• That is the name to use in the macro
• Click the Cancel button, to close the Slicer Settings, without making any changes.

Test the Slicer

Next, to test the slicer, follow these steps:

• First, click on one of the city names in the Slicer
  • The pivot table is filtered, by the City field, and will only show the selected city.
• Next, to clear the filter, click the Clear Filter button, at the top right of the slicer
  • All the cities are visible in the pivot table again

5) Add Worksheet Code

The next step is to add Excel VBA code to the worksheet module, for the sheet where the short city list is stored.

1. Right-click on the sheet tab for the city list sheet, and click View Code
2. Copy the below code, and paste it onto the sheet's code module.
3. Change the slicer names, to match the slicer names in your file (see the slicer name in Slicer Settings)
4. Save the file as macro-enabled.

6) Move the Slicers

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:

1. Right-click on the slicer, and click Cut
2. Select the sheet where you want to put the slicer
3. Right-click on the worksheet, and click Paste

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.

• On one sheet, click a Region name in the Slicer
• On other sheet, table is automatically filtered to show the selected Region

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.

• The tables both have food sales data, but some of the columns are different.
• Both tables have a Region column, with the same two regions - East and West
• The tables, Slicers and pivot tables are colour coded:
  • Sales Data 1 - green
  • Sales Data 2 - blue

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.

Download the Sample Files

1. Short City List: To download the completed workbook with the short city list Slicer, click here. The zipped file contains macros, so enable macros to test the slicers.
2. Two Sets of Slicers: This workbook is based on the short city list example, but it has two sets of Slicers -- one for City, and one for Category. The zipped file contains macros, so enable macros to test the slicers.
3. Multiple Excel Tables: The workbook has two Excel tables with sales data, and each table has a hidden pivot table, and a pivot table Slicer. Pivot Slicers are on the table sheets, and when clicked, all the Slicers are updated, and the 2 tables are filtered. The zipped file contains macros, so enable macros to test the slicers.

More Pivot Table Resources

FAQs - Pivot Tables

Slicers, Pop Up

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table