Slicers

Quick reference

• 00:04 In this video, we are gonna look at one of the coolest things that got added to
• 00:08 a pivot table that started in Excel 2010 and carries through with later versions.
• 00:14 These are called slicers.
• 00:15 One of the big issues with the filters inside a pivot table
• 00:18 is that users really freak out.
• 00:20 They don't wanna check them, they don't want to change them because they're afraid
• 00:23 that they might break their report.
• 00:24 It's a little bit of a fear factor.
• 00:26 Well this all gets solved with slicers.
• 00:28 Slicers are amazing, and you're gonna see how this works here.
• 00:31 So, the way that we create a slicer, is we click inside our pivot table table.
• 00:35 And then in Excel 2013 we go to pivot table tools, we go to analyze.
• 00:40 In Excel 2010, it's pivot table tools, options.
• 00:43 Same tab, different name.
• 00:45 On that tab you're gonna find the insert slicer button.
• 00:48 So we click insert slicer.
• 00:50 And we get a list of all the pivot table fields.
• 00:53 Now I'm gonna grab class and category and, why not, we'll grab week as well.
• 00:57 And we'll say OK.
• 01:00 And that creates these little boxes that are actually filters.
• 01:07 And you can see this because when I go and
• 01:09 click on alcohol, did you see my pivot table refiltered?
• 01:13 And if I click on, say, canned beer, it's gonna filter my pivot table right down.
• 01:18 And if I click on on week two, it'll make it even shorter.
• 01:21 Now, you notice a couple things here.
• 01:23 Number one it's filtering stuff in, which is awesome.
• 01:25 It's also bouncing my filters all over the place.
• 01:27 So there's some configuration stuff that I need to do here.
• 01:30 The other thing is, is that I've clicked on alcohol,
• 01:33 it's driven me into the alcohol area of my table.
• 01:36 So I probably don't need this class on here anymore.
• 01:39 I can pull it off.
• 01:41 Now, what I'm gonna do is I'm gonna make my header row a little bit bigger here,
• 01:45 and I'm gonna grab all of these guys.
• 01:47 So I'm clicking on one, gonna hold down my control key, and click on the next two.
• 01:51 Gonna drag them to the top here,
• 01:53 and I'm just gonna arrange them so they're in a little bit nicer state.
• 01:56 So, on the slicer tools tab, which comes up when you select your slicer, we can go
• 02:00 to options, align, and align top, and that's get them at the same height.
• 02:06 Because I haven't made any modifications to the height that's there,
• 02:10 I can drag the bottom border up, and I can get it into a nice spot where I can just
• 02:15 see the bottom edge of the little bubble on number three and nothing below that.
• 02:20 There we go, that's perfect.
• 02:22 And I'm also gonna make a very quick change that's really important.
• 02:25 Because there all still three of these guys selected,
• 02:27 I'm gonna right click on these guys, and I'm gonna say size and properties.
• 02:33 And inside size and properties, there is a properties area.
• 02:37 In Excel 2010 you'll have a properties tab inside instead.
• 02:41 The key pieces that I want are, don't move, or size with cells.
• 02:45 This is kind of the same thing as selecting in your pivot tables saying,
• 02:49 don't adjust my column widths every time I update it.
• 02:51 So every time the pivot table updates its column widths,
• 02:54 my slicers will stay put if I've got this don't move or size with cells set.
• 02:59 I could also uncheck the printing if I wanted to at this point.
• 03:02 At any rate, now that I've got that done, you're gonna click outside of here.
• 03:06 We can get rid of this, don't need it anymore.
• 03:08 And I'm gonna just drag these guys around a little bit maybe.
• 03:11 We can make class just a little bit narrower cuz I don't need
• 03:13 all of that space for words.
• 03:15 In a week here, I'd like to have a few more columns so I can see what's going on.
• 03:19 So we're gonna click columns on slicer tools.
• 03:22 That's better.
• 03:23 And this guy here, we'll just make him a little bit wider as well.
• 03:26 Maybe we'll add an extra column there.
• 03:31 That's good enough.
• 03:33 And you can see now that we can drive through our pivot table
• 03:36 by clicking on these buttons.
• 03:38 Isn't that awesome?
• 03:39 These things just beg to be clicked.
• 03:41 When you put something like this in front of a user, unlike the old little filter
• 03:45 controls, where they sit there and go, oh, I'm too scared of that.
• 03:48 These things are like, oh, something shiny, click, click, click, and
• 03:51 they get excited about it.
• 03:52 It's really really cool.
• 03:54 Now, couple of things we need to know about slicers, though.
• 03:57 How do we select multiple objects?
• 03:59 How'd I just do this?
• 04:01 The secret is to hold down your Control key when you're clicking.
• 04:05 So if I hold down Control, and I click draft beer, and then let go,
• 04:08 you'll see that it's now going to work for me.
• 04:12 And If I want to clear the filters all together, I click the little red X,
• 04:18 and my pivot table unfilters.
• 04:21 One last thing I wanna tell you about slicers, you can also apply
• 04:26 slicer styles just like you can with a pivot table to anyone of these guys.
• 04:30 And in fact, just the same as a pivot table, you can go through,
• 04:34 you can actually create your own styles by duplicating them.
• 04:37 I won't do that here, but it's just something to be aware of that you have
• 04:41 that complete freedom and flexibility.
• 04:43 Slicers are amazing, and
• 04:44 I highly recommend you use them because they get your users to engage with your
• 04:48 work and drill in and answer their own questions without phoning you.
• 04:51 They're just awesome.

Lesson notes are only available for subscribers.