Locked lesson.
About this lesson
Highly visible and engaging, these devices can provide your users with a method to easily select valid object or filter in to just their slice of the pie
Exercise files
Download this lesson’s related exercise files.
Filtering and linking Dashboards using Slicers.xlsx50.6 KB Filtering and linking Dashboards using Slicers - Completed.xlsx
52.3 KB
Quick reference
Filtering and Linking Dashboards Using Slicers
Interactive filtering of Pivot Tables using Slicers.
When to use
Slicers are useful for giving a user friendly way to cross filter and drill into Pivot Table based dashboards.
Instructions
Groundwork: Friendly names for your Pivot Tables
- Select your PivotTable and go to PivotTable Tools --> Analyze
- On the far left side, provide a more friendly name than PivotTable1
- We suggest a short but descriptive name to separate Pivot Tables from ranges and charts
- Each PivotTable should be provided its own descriptive name
Adding Slicers
- Click any cell in a Pivot Table and go to PivotTable Tools --> Analyze --> Insert Slicer
- Choose the field you want to use for filtering
Working with Slicers
- Selecting any button will filter the PivotTable to only record those that include that data point
- Hold down SHIFT or left click and drag to select multiple contiguous items
- Hold down CTRL to select multiple non-contiguous items
- Click the clear button (with the red x) to clear all filters from the slicer
Formatting Slicers
- All formatting is done by selecting the slicer and going to Slicer Tools
- Options include setting slicer styles, and increasing the number of columns
Linking to multiple pivots
- Select the slicer and go to Slicer Tools --> Report Connections
- Link to the other Pivot Tables you wish to cross filter
- As you select any item in the slicer, it will automatically slice all Pivots
Hints & tips
- Slicers are a fantastic way to give users a friendly interface for slicing and dicing
- Giving your PivotTables descriptive names makes it much easier to select the correct Pivot Table when linking
- 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.