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 then follow this post and you will learn a superb trick to create some interactive dashboards.
Yes, that’s right.
Let’s say you are working on a dashboard where you are using multiple pivot tables.
If you are able to connect a slicer to all the pivot tables you can control entire dashboard with a single slicer.
In above example, we have a single slicer to control both of the pivot tables.
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 Analyze → Filter → Insert Slicer.
- 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 Analyze → Slicer → Report Connections.
- You will get a new dialog box with the list of pivot tables which 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.
? click here to read rest of the 99 pivot table tips and tricks can make you a PIVOT TABLE PRO in 2019
Wrap Up
I love to link a slicer to multiple pivot tables when I have to work on dashboards.
Important: You can only connect a slicer with more than one pivot table using the same data source.
Do you like this tip?
Share your views with me in the comment section, I'd love to hear from you and please don't forget to share this tip with your friends.
You must Read these Next
- Refresh All Pivot Tables at Once: In this post, I’d like to show you 3 simple methods which you can use to...
- Group Dates in a Pivot Table: In this post, I’d like to show exact steps to group dates in a...
- Pivot Table Timeline: In this post, I’d like to share with you simple steps to add a timeline filter...
- Automatically Update a Pivot Table Range: In this post, I’d like to share with you three...
- Conditional Formatting to a Pivot Table: In this post, you will learn how to apply conditional formatting...
- Running Total in a Pivot Table: In this tutorial, I will show you how to add a running total in a pivot table...
About the Author
Puneet Gogia
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
hi does the slice on excel 2016 work for more than 2 pivots?
i am unable to link to more than 2 pivots
Can you add the original data table to the post.
Hi Punnet, Can you share POWER BI tricks
Puneet, thanks for this post, it helped me out!
However, please pay attention to the fact that steps 7 and further are slightly different, at least in Office 365 ProPlus (Version 1907 (Build11901.20176))
The ribbon option is titled “Filter Connections” and then it lists existing slicers.
Tell me if you wish to receive printscreens.
Yup send me.
You instructions just did not work
What went wrong?
I liked very much, thanks
You’re Welcome
once I reached report connecter, the sheet showing only one
Can I have a screenshot?
The books are useful.
Thanks & Regards.
Hi Puneet
Many thanks for sharing how to link one single slicer to multiple pivot tables. Your instructions plus your Excel example file gave very clear instructions.
Regards, Victor
I’m glad you liked it, Victor.
Can we connect two Pivot Table of different sources but have one common Factor/Heading?
If Yes, How?
Only when you create a pivot table through combining those two sources.
Please explain with example and workbook.
or share the link if already done.
———
I am unable to go to Analyze.
Actually I could not find it.
Where it is?
When you select a pivot, you’ll two extra tab on the ribbon. Try again.