Filtering and Linking Dashboards Using Slicers

Quick reference

• 00:04 Now pivot tables have a really cool little control that we can use to filter
• 00:09 a pivot table down and actually show less data, which is pretty neat.
• 00:14 So what I'd like to do now, is I'd like to go and actually add one of those guys and
• 00:18 show you how we can actually link it to multiple pivot tables at the same time.
• 00:22 Before we do that though, I think it's a really good idea to go and
• 00:25 just take a really quick look at our PivotTable names.
• 00:29 So, if we go to the PivotTable Tools Design tab, we get all kinds of different
• 00:33 formatting options, but over on the PivotTable Tools Analyze tab,
• 00:38 you'll notice at the very first little option here is a PivotTable Name.
• 00:41 Much like we can name tables, or we can name charts, we can name pivot tables.
• 00:46 And it's a really good practice to do this, cuz I've got PivotTable1, and
• 00:49 I've got PivotTable2.
• 00:51 Those are not very descriptive.
• 00:52 So why don't I come back and say, you know what, instead of PivotTable1, why don't I
• 00:56 call it something like pvt, for pivot table, CatSum, for category summary.
• 01:02 There we are.
• 01:04 And now I'll go over to my other pivot table, and I'll call this one pvt,
• 01:08 again for pivot table, MonthlySales.
• 01:13 Whatever nomenclature you want to use to name your pivot tables is
• 01:16 totally up to you.
• 01:18 I'm just suggesting right now, and you'll see why in a second,
• 01:20 that this might make a good idea.
• 01:23 And it's unfortunate that we don't get prompted for
• 01:25 this when we actually build our pivot tables by default,
• 01:27 to give us the option to set this to a descriptive name.
• 01:30 Now, again, I'm just going to go and
• 01:32 dismiss my PivotTable Field List because I don't really need that right now.
• 01:36 What I want to do now is add a slicer that allows me to filter my
• 01:41 pivot tables down to only keep the records that are actually applicable.
• 01:45 So I really want to be able to say, just show me the stuff that's relevant
• 01:48 to tents, and have it filter my category pivot table down to only show tents.
• 01:53 But also filter out anything from this particular table that
• 01:56 isn't based on tents.
• 01:58 So, to do that, I'm going to go and, first, select inside a pivot table.
• 02:04 That's kind of an important piece,
• 02:05 because the ability to insert a slicer is on the PivotTable Tools Analyze tab.
• 02:11 Now, again, my button may be smaller than yours.
• 02:14 You may have a full sized button, but regardless,
• 02:16 when we click it we get this little pop up that comes up.
• 02:19 And it says, hey would you like to insert a slicer?
• 02:22 What field would you like to slice on?
• 02:25 This is all the different field names that are available for your pivot table.
• 02:29 I'm going to grab Category and say okay.
• 02:32 And what you'll see here is, because I started working
• 02:35 with this particular table, if I were to go and click on tents,
• 02:40 it's gonna filter this down to only show the data that's relevant for tents.
• 02:44 But it doesn't touch my other table whatsoever.
• 02:47 We can go to tarps and ground sheets,
• 02:48 it will filter the right hand table, sleeping bags, accessories.
• 02:52 If I want to multi-select, I have a couple of different options.
• 02:56 I can left-click and drag, which will select a contiguous block.
• 03:00 I can click on one, hold down my CTRL key, and
• 03:03 click on others, that will allow me to select multiple items.
• 03:07 Or if I'm in Excel 2016, I can click on the multi-select box, and
• 03:12 then turn on or off the components that I do or don't want, and uncheck it.
• 03:17 But that only came in in Excel 2016.
• 03:19 To clear the slicer altogether, we click on Clear Filter and
• 03:22 it brings everybody back.
• 03:24 Now, let's format the slicer a little bit first.
• 03:28 So I'm gonna move him up here and again hold on my ALT key and
• 03:30 just snap him in place, and I'm going to redesign the slicer so
• 03:35 that fits into the area that I actually laid out.
• 03:39 Now that's cool and everything else, but I've got this little drop down or
• 03:42 scroll bar, but
• 03:43 I've got lots of room over here which I could actually have multiple controls.
• 03:47 So why don't I go up to the Slicer Tools Options, and index up my columns by one,
• 03:52 and you'll notice that that actually gives me a pretty nice looking little slicer.
• 03:56 Again, it's here, I can go and I can click and I can play, and it slices well.
• 04:02 Except that it's only slicing one pivot table.
• 04:05 And this is the cool secret, because this is the user interface-driven
• 04:08 way to actually go and slice and dice your data.
• 04:11 And the neat thing about this is that we can actually hook it to multiple
• 04:15 pivot tables.
• 04:16 And this actually even keeps a pivot table in sync,
• 04:18 which is really hard to do before a slicer came out.
• 04:21 So the way that we do it, is we select our slicer, and
• 04:24 we're gonna go to Slicer Tools, Options, and we click on Report Connections.
• 04:29 And when we click on report connections, it comes back and
• 04:32 it says hey, sure, what pivot tables would you like me to link it to?
• 04:36 And now let me ask you this, do you see why we renamed our pivot tables?
• 04:40 Can you imagine if you had 30 Pivot tables in a workbook and
• 04:43 they were called PivotTable1 through 30?
• 04:46 Man, that'd be a nightmare trying to figure out where they should
• 04:48 be connected to.
• 04:49 So by renaming the pivot table, I can say hey,
• 04:51 I wanna link this one to the monthly sales and the pvtCatSum.
• 04:56 I can pick one or as many or all the pivot tables as I want to link this to.
• 05:01 So I can have one slicer drive multiple pivot tables.
• 05:04 I can even create multiple slicers to drive multiple pivot tables.
• 05:07 It works out really nicely.
• 05:08 And what you'll see now is when I click on tents,
• 05:11 it filters that table, and sleeping bags, accessories.
• 05:16 By going to multi-select, you can see that I can actually load these guys up or
• 05:20 down as I want and it filters both.
• 05:22 And it keeps the pivot tables in sync nicely.
• 05:24 So that is the cool thing in using slicers with our data, is that we get a beautiful,
• 05:30 clickable, inviting interface that we can customize to a degree.
• 05:35 You can set your different colors and different stuff like that as well,
• 05:38 that we can use to drive our dashboards if they're based on pivot tables.
• 05:42 How cool is that?

Lesson notes are only available for subscribers.