Tech Tips

  • Tech Tips
  • 03.24.20

Using Slicers to Update Pivot Tables

  • by Brooke Kile, Director of Institutional Research, Marian University

For information where online dashboards are not available, my office frequently uses pivot tables to visualize large sets of data for our stakeholders. Often times, we use the same data set for multiple visualizations like in the view below. 

Same data set with multiple visualizations

Note: Data shown for demonstrative purposes only. 

Unfortunately, if a user were to select a different sport on one pivot table the other two below it would not automatically update. This can easily cause confusion, so, instead, I opt to use a Slicer to update all of the pivot tables.

  1. Click on the first pivot table. From the Excel ribbon at the top select ‘insert’ and then select ‘Slicer.’
    How to navigate to Slicer tool
  2. Select the slicer field. In my case, I want the ‘Sport’ to update across all pivot tables. Click OK when completed.

    Select the slicer field
  3. Use the slicer tool to update the number of columns and format as needed.
    Slicer Tool Example

In the same slicer below, it has been updated it to reflect 7 columns instead of 1. Slicer Example 2

Use slicer style to adjust fonts. Choose a style type and then click ‘New Slicer Style.’ 

Slicer style options

Click on ‘Format’ (before this step, it is optional to give this custom slicer style a name). 

Format slicer style

Adjust the font to the desired preferences. Click ‘OK’ when complete. 

Slicer font options

Clicking on ‘Men’s Bowling’ (highlighted in the Slicer Tool) will update the first pivot table; however, in order for all pivot tables to update they need to be connected to the slicer. In the example below, the second pivot table still reflects ‘Men’s Baseball.’ The third pivot table (not shown) also still reflects ‘Men’s Baseball.’

Slicer Example 3

To connect the Slicer, right -click on the Slicer and select ‘Report Connections.’ 

Connect the Slicer

Click the appropriate tables to connect. In this case, I have three Pivot tables. I want both PivotTable2 and PivotTable3 to be added to the connection. Click OK when you're finished. 

Connecting additional pivot tables to slicer

Now all tables are connected. If I select ‘Women’s Cycling’ on the Slicer, all of my pivot tables update to that sport selection.

Slicer Example 4

Tip: If each of your pivot tables don’t automatically connect, check that your pivot tables are using data from the exact same data source, including the number of columns selected in the shared data source.

See also: The December 2018 Tech Tip shows how to use a slicer function to ‘clear all’ selections.

Back to Tech Tips