• 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. 

TTMarch20201

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.’
    TTMarch20202
  2. Select the slicer field. In my case, I want the ‘Sport’ to update across all pivot tables. Click OK when completed.

    TTMarch20203
  3. Use the slicer tool to update the number of columns and format as needed.
    TTMarch20204

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

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

TTMarch20206

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

TTMarch20207

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

TTMarch20208

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.’

TTMarch20209

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

TTMarch202010

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. 

TTMarch202011

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

TTMarch202012

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.