Slicer for Two Data Sets

Learn Excel from MrExcel Podcast, Episode 2198: A Slicer for Two Data Sets.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was in New Jersey doing a seminar there, and Rick asked a question, he said, "Hey, look, I have pivot tables built on two different data sets and I'd like one slicer to be able to control those." And now I did a video about this-- Episode 2104-- that used some VBA, but this video has really caused a lot of problems because people have slicers based on data that doesn't match. And so, you know, I wondered if there was an easier way to do this without the VBA.

And so, I have a table here on the left that has Sector, and I have a table on the right that has Sector. And if I have any existing pivot tables on these two data sets, I need to get rid of those pivot tables-- I need to just completely start over. And what we're going to do is we're going to build a third table that's going to live between the other two tables, and that table is just going to be really simple-- it's just going to be a list of all of the Sectors. So I take the Sectors from the left table, I take the Sector's from the right table, paste all of these together, and then choose the whole set, and under Data, choose Remove Duplicates-- right here-- and we end up with just the unique list of Sectors. Alright? Then we have to take each of these tables and make them into-- Format as Table using Ctrl+T, alright. So I take the left one, Ctrl+T; "My table has headers", Yes; second one, Ctrl+T, "My table has headers, Yes; third one, Ctrl+T, "My table has headers". Now Microsoft gives these really boring names, like "Table 1", "Table 2", and "Table 3", and I'm going to rename those-- I'm going to call this left one Sales, I'm going to call the middle one my Slicer Source, and then this one over here I'll call Prospects. Alright.

So, I have the three tables and I need to somehow teach Excel that this table is related to both this table and this table over here. So we come to Relationships-- Data, Relationships, and I'm going to create a New Relationship from the Sales table. It has a field called Sector that's related to the Slicer Source-- Sector, click OK. Now create another Relationship from the right side, from the Prospect table-- it has a field called Sector, it's related to Slicer Source, the field called Sector, click OK.

So, now, I've taught Excel how what the relationship is, both from this one to the Slicer Source, and from this one to this Slicer Source. Now, at this point, I can build my two pivot tables. So I start here, Insert, PivotTable, make sure to check the box for "Add this data to the Data Model, and we can build a nice report of Customer, and maybe Revenue-- like that. I want to see this high to low-- so Data, Z to A, and I want to narrow it down to just the top 5, or top 3, or something like that. Great, okay. Then, I want to build a second pivot table that's using the second data set. So, from here-- Insert, PivotTable, again make sure to "Add this data to the Data Model", this time I'll put it on the same sheet, so we can kind of see how they are interacting with each other. Click OK. And we will get a unique count of prospects. It starts out with a count of prospects, but if I go into Field Settings, because I'm using the Data Model, I have an extra calculation here at the bottom called Count-- Distinct Count. Click OK, and we'll put the Sector here so we can see how many prospects there were in each of those Sectors. Okay, beautiful, that's all working great.

Now, what I want to do is insert a slicer, but the slicer's not going to be based on the Sales table, nor the Prospects table; that slicer is going to be based on the Slicer Source. Alright, so we choose a new slicer based on the Slicer Source, field is Sector, we get our slicer here, change the color if you'd like. Okay, so, just do a test here-- choose, for example, Consulting, and you see that this pivot table is updating but that pivot table is not updating. So from that pivot table, go to the PivotTable Tools-- Analyze, Filter Connections, and hook that pivot table up to the Sector Filter. And then as we choose, you see that this pivot table is updating, and that pivot table is updating as well. No VBA whatsoever.

Hey, be sure to check out my new book, MrExcel LIVe, The 54 Greatest Tips of All Time. Click that "I" on the top right-hand corner for more information.

Today, Rick from New Jersey asked if one slicer can control pivot tables that come from multiple sources. And while I've done this in Episode 2104, with a VBA solution, we can do without VBA using the Data Model. That requires Windows, version of Excel-- Excel 2013 or newer-- and if you have any pivot tables that are not based on the Data Model, delete those, find the fields 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. Now, you have three data sets-- the original data set, the other data set, and this new one. Make each one into a table using Ctrl+T; build a Relationship between the left data set and this new table; between the right data set and the new table; and then as you build your two pivot tables for each one, say "Add this data to the Data Model"; when you build a slicer you have to click the All tab to see that third table; choose from the Slicer Source, that tiny little table; and then one of the two pivot tables is not going to be tied to the slicer; select a cell in that pivot table; use Filter Connections to connect the pivot table and the slicer.

To download the workbook from today's video, visit the URL in the YouTube description, and, you know, you can download the book.

Well, I want I thank you for stopping by, I'll see you next time for another netcast from MrExcel.