Select the slicer and go to Slicer → Report Connections, or right-click the slicer and choose Report Connections. Tick every pivot table you want it to control and click OK. One click on the slicer now filters all of them, even on other sheets.
To create the slicer first, click any cell in one of the pivot tables and go to PivotTable Analyze → Insert Slicer, tick the field and click OK. From the pivot side, PivotTable Analyze → Filter Connections does the same job in reverse: it lists the slicers that pivot table can use.
Only pivot tables built from the same source range or table appear in the list, because they share one pivot cache. If a pivot table is missing, it was built from a different source; rebuild it from the same table as the others.
For pivot tables built from two different tables, tick Add this data to the Data Model when you create each one, relate both tables to a lookup table of unique values, such as a list of regions, in Data → Relationships, and build the slicer from that lookup table. It can then connect to every Data Model pivot table in the workbook.
Once a slicer links several pivot tables, Excel will not let you point one of them at a different source. Untick it in Report Connections, change the source, then reconnect it. Timelines connect the same way, through Timeline → Report Connections.
Sometimes, using more than one pivot table is a mess. But it’s not a mess if you connect all the pivot tables with a single slicer. So, do you know how to link a slicer with multiple pivot tables?
If you don’t know, follow this post, and you will learn a superb trick for creating interactive dashboards.
Yes, that’s right.
Let’s say you are working on a dashboard with multiple pivot tables. If you can connect a slicer to all the pivot tables, you can control the entire dashboard with a single slicer.
In the above example, we have a single slicer to control both pivot tables.
NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.
Steps to Connect a Slicer to Multiple Pivot Tables
Below are the steps which you need to follow to create a multi-linked slicer:
- First of all, take two or more pivot tables to connect a slicer.
- After that, select a cell in any of the pivot tables.
- From here, go to PivotTable Analyze → Filter → Insert Slicer (the tab is called Analyze in Excel 2016 and 2019).
- Now from the “Insert Slicer” dialog box, select the column to use as a filter in the slicer and click OK.
- At this point, you have a slicer in your worksheet which can filter the pivot table in which you insert it.
- Next, you need to connect it to the second pivot table.
- From here, select the slicer and go to the Slicer tab → Slicer → Report Connections (or right-click the slicer and select Report Connections).
- You will get a new dialog box with the list of pivot tables that are in your workbook.
- In the end, just tick mark all the pivot tables and click OK.
Congratulations! You have connected the slicer with two pivot tables.
Why a Pivot Table Is Not in Report Connections
The Report Connections dialog box only lists the pivot tables that are built on the same data source (the same pivot cache) as the pivot table you inserted the slicer from. So, if a pivot table is missing from the list:
- Different source: it’s made from another range or table. Change its data source (PivotTable Analyze → Change Data Source) to the same table as the other pivot tables.
- Separate cache: it uses the same range but was created as a separate cache. Recreate it from the same Excel table, or copy and paste an existing pivot table and change its fields, so both share the same cache.
- Data Model: pivot tables made from the Data Model can only connect to slicers made from the Data Model.
The best way to avoid this problem is to convert your data into an Excel table (Ctrl + T) before you create the pivot tables and use that table as the source for all of them.
Disconnect a Pivot Table from a Slicer
To remove the link, open Report Connections again and untick the pivot table. It’s a good idea to do this before you change a pivot table’s data source, as Excel can refuse the change while the pivot table shares a slicer with other pivot tables.
Frequently Asked Questions
Can one slicer control pivot tables on different sheets?
Yes. Report Connections lists all the pivot tables in the workbook that share the same data source, whichever sheet they are on.
Can a slicer control a pivot chart?
Yes. A pivot chart follows its pivot table, so when the slicer filters the pivot table, the chart changes too.
Does a timeline work the same way?
Yes. A timeline also has Report Connections, so you can connect one timeline to several pivot tables with the same data source.
thanks a lot for this – grateful
I am using Office 2016
1. I created two pivot tables side by side similar to what you have in your tutorial
2. followed your steps above > This did not work
It keeps popping up just the other data table on another sheet that was used for this pivot table and the original data page and not the second pivot table on this sheet1 page. It does not find the secondary pivot table on the same sheet.
– they are created from different data sources, but have some similar information (Columns) that I wish to use as a slicer in both…. does not work.
The enable show PT data allows user by double clicking to see all the source data Fields in from source !, Is there a way that once you double click on PT, Only the Filtered result Data values fields Details to be shown to the PT user, by ignoring other row fields. Hope it clears to you. Example my source data has Salary for all employees, however don not want to show all columns to PT user