Using Slicers to Update Pivot Tables
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.
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.
- Click on the first pivot table. From the Excel ribbon at the top select ‘insert’ and then select ‘Slicer.’
- Select the slicer field. In my case, I want the ‘Sport’ to update across all pivot tables. Click OK when completed.
- Use the slicer tool to update the number of columns and format as needed.
In the same slicer below, it has been updated it to reflect 7 columns instead of 1.
Use slicer style to adjust fonts. Choose a style type and then click ‘New Slicer Style.’
Click on ‘Format’ (before this step, it is optional to give this custom slicer style a name).
Adjust the font to the desired preferences. Click ‘OK’ when complete.
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.’
To connect the Slicer, right -click on the Slicer and select ‘Report Connections.’
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.
Now all tables are connected. If I select ‘Women’s Cycling’ on the Slicer, all of my pivot tables update to that sport selection.
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.