Microsoft Excel Tutorial: Excel Slicer For Pivot Tables From Different Data Sets.
Welcome to the MrExcel netcast, where you can learn all about Excel. How to use one slicer to control pivot tables from two different data sets. This question was posed by Rick from New Jersey during one of my seminars, and I am excited to share the solution with all of you.
In the past, I have addressed this issue in Episode 2104 using VBA, but this has caused some problems for users who have slicers based on data that doesn't match. So, I decided to find a simpler solution without the use of VBA. And I am happy to say that I have found a way to do this using the Data Model in Excel 2013 or newer.
To begin, you will need to have two pivot tables built on two different data sets. If you have any existing pivot tables that are not based on the Data Model, you will need to delete them. Then, you will need to find the fields that are common between the two data sets and copy them into a new table. Use the "Remove Duplicates" function to get a unique list of these fields. This new table will act as a bridge between the two data sets.
Next, you will need to format each of the three tables as a table using Ctrl+T. Microsoft may give these tables boring names like "Table 1" or "Table 2", but you can easily rename them to something more meaningful. For example, you can name them "Sales", "Slicer Source", and "Prospects".
Now comes the important step - creating relationships between the tables. Under the "Data" tab, click on "Relationships" and create a new relationship from the Sales table to the Slicer Source table, and another one from the Prospects table to the Slicer Source table. This will teach Excel how the tables are related to each other.
With the relationships established, you can now build your two pivot tables. Make sure to check the box for "Add this data to the Data Model" when creating the pivot tables. You can then customize the pivot tables as desired, and they will be automatically connected to the Slicer Source table.
To add a slicer, go to the "Insert" tab and choose "Slicer". However, instead of selecting the Sales or Prospects table, choose the Slicer Source table. This will give you a slicer with all the fields from the Slicer Source table. You can then connect this slicer to one of the pivot tables by going to "PivotTable Tools" ＞ "Analyze" ＞ "Filter Connections" and selecting the pivot table you want to connect it to.
And that's it! You now have one slicer controlling pivot tables from two different data sets without the use of VBA. To download the workbook used in this video, check out the link in the YouTube description. And don't forget to check out my new book, MrExcel LIVe, The 54 Greatest Tips of All Time. Thank you for watching and I'll see you in the next netcast from MrExcel.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
This video answers these common search terms:
how to use a slicer on multiple tables excel
how to create one slicer for multiple pivot tables in excel
excel how to connect tables to slicer
how to sync slicer excel
excel how to connect two slicers
how to combine slicers in excel
how to connect two slicers in excel
how to link two slicers in excel
Table of Contents:
(00:00) One Slicer for Two Data Sets
(00:21) Exploring a non-VBA solution for controlling pivot tables with one slicer
(01:03) Creating a third table with a unique list of sectors
(02:06) Formatting tables and renaming them
(02:56) Establishing relationships between tables
(03:29) Building pivot tables and adding them to the data model
(04:14) Inserting a slicer based on the Slicer Source table
(05:10) Clicking Like really helps the algorithm
Rick from NJ asks if one slicer can control Excel pivot tables that come from multiple sources.
See episode 2104 for a VBA solution
But this can be done with the Data Model in Excel.
First - delete any pivot table that are not based on the data model.
Find the field(s) in common between your two data sets.
Copy each field to a new table and use Remove Duplicates to get a unique list of that field.
Make each of the data sets in to a table using Ctrl+T.
Build a relationship between each data set and the new Slicer Source table.
When you build each pivot table, choose Add this Data to the Data Model.
Build a Slicer - click the All tab - choose from the Slicer Source.
Figure out which pivot table is not tied to the slicer.
Select a cell in that pivot table and use Filter Connections to connect the pivot table to the slicer.
Also in this episode: The Data Model allows Count Distinct.
Download the workbook from [ Ссылка ]
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
