It’s quite common, particularly on dashboards, that we present multiple pivot tables that are based on the same dataset. These dashboards or workbooks are likely to be reviewed by someone more senior and it’s entirely likely that that reviewer may not know much about Pivot Tables. They may not understand the Filter pane or that filtering one pivot table does not filter any others. Also, if we are working on a dashboard then we dont necessarily want reviewers to be having to press lots of different buttons to get to the answer that they want.  To be honest that’s a recipe for getting the wrong answer! We may not want the reviewer going into the Field List at all.

A great alternative to adding in fields into the Filters pane of the Field List is to add in Slicers. 

Slicers do the same thing as Pivot Tables Filters. They filter Pivot Tables (they’re also available for filtering Datatables if you’re interested) and are essentially just text boxes with buttons in them.  However, they have some distinct advantages over Pivot Table filters.

a) They can be formatted and so look better
b) They can show all of the values in a field at once so a reviewer can instantly know what they’re looking at
c) They’re more intuitive for those that are not familiar with Pivot Tables
d) They can be used to filter multiple pivot tables at once!

Inserting a slicer

To insert a slicer, left click on any of the Pivot Tables and then select the tab Pivot Table Analyze. On the ribbon, select Insert Slicer.

A menu then pops up asking which of the Fields in the Pivot Table you want to use to create the slicer. Once you’ve selected your field then the slicer (s) will pop up and you can then resize them and format them however you like.

How can I use a slicer to filter multiple pivot tables at once?

Easy! Right click the slicer, and then on the resulting menu, select Report Connections. This will bring up a pop up menu showing all the pivot tables in the workbook that have been created using the same dataset. Simply then select all the Pivot Tables that you want the slicer to filter.