One of the drawbacks of pivot tables (previously) was that if you built many pivot tables in the same workbook to create dashboards, it was difficult to set up a control sheet to change a particular filter on all your pivots. For example to change all the pivots to only show a certain division. In order to link pivot table filters you needed to use VBA or record a macro.
However it is now possible to achieve the same thing using the slicer options.
You can connect a single Slicer to multiple report to create interactive dashboards. This is useful if you want all the pivot tables in a spreadsheet to change when you click on the slicer. The method to do this (once you have set up some slicers):
- Click on the Slicer that you want to use to control multiple pivot tables or pivot charts
- Click on the SLICER ribbon items and click Report Connections (shown below)
- Tick all the Pivot Tables (and/ or Pivot Charts) that you want to be connected and click OK.
Now whenever you change the Destination Network Slicer in the example above, both Pivot Tables will change to reflect your choice.