Filtering Multiple Pivot Tables with One Button
Sometimes we need to have multiple PivotTables in a single sheet or workbook that analyse the same information but which are segregated by different fields; this is quite common, particularly on dashboards!
These dashboards or workbooks are likely to be reviewed by someone more senior and it’s entirely possible that that reviewer may not know much about PivotTables. They may not understand the Field List or that filtering one PivotTable does not filter any others. Also, if we are working on a dashboard then we don’t necessarily want reviewers to have 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.
Maybe one PivotTable has Sales segregated by Location and another PivotTable has Sales segregated by Supplier. If we want to filter those PivotTables by the same field, say by Year, then that’s longwinded as you have to put the Year filter on all the PivotTables. However you can also use Slicers to filter multiple PivotTables at once…
What are Slicers you say…?
In this lesson we look at how you can filter multiple pivot tables, first through adding fields to the Filters pane in the Field List in each of the Pivot Tables and then through adding a SINGLE Slicer. We also cover what a slicer is and why using slicers could be better for any analysis that is going to get reviewed compared to just adding filters to a PivotTable.
Resources
Symbols
Video Chapters
- 01:01 – Do your Pivot Tables need to be connected to the same dataset?
- 01:19 – How could I filter multiple pivot tables without slicers?
- 02:20 – What are slicers?
- 02:25 – Why can Slicers be better than Pivot Table filters?
- 02:58 – How can I insert a slicer?
- 04:20 – How can I use a slicer to filter multiple pivot tables at once?
Short On Time?
Instructions
a) Create a pivot table, by highlighting your data and then selecting the ‘Insert’ tab and then selecting ‘Pivot Table’ on the ribbon.
b) To insert a slicer, left click on any of the Pivot Tables and then select the tab ‘PivotTable Analyze’. On the ribbon, select ‘Insert Slicer’.
c) A menu then pops up asking which of the Fields in the PivotTable 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.
Instructions
a) 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.
b) Simply then select all the PivotTables that you want the slicer to filter.
…and a little more
Clearing Slicers – To clear your slicers, just click the filter with a cross button in the top right hand corner of the slicer.