Slicers

Quick reference

• 00:04 In this video I'm going to show you I think, one of the coolest ways to actually
• 00:07 filter a PivotTable and we're going to jump straight into it here.
• 00:11 What we're going to do is we're going to select our cell inside our PivotTable,
• 00:13 anyone at all, can be in the middle can be anywhere else.
• 00:16 We're going to go to PivotTable Tools > Analyze, and
• 00:18 we're going to choose Insert Slicer.
• 00:21 Now when we do that we get prompted with the entire field list here and
• 00:24 I'm going to go grab a few different things.
• 00:26 I'm going to grab class, I'm going to grab category and I'm going to grab week and
• 00:30 now I'm going to say okay.
• 00:32 And at this point you're going to see that it gives us these three funky little boxes
• 00:36 now what's really cool about these things is that
• 00:39 these little arrows that you have on your PivotTable here.
• 00:42 People get really freaked out when you ask them to click those things, right?
• 00:45 They look at it and they go, no, I'm going to break it.
• 00:47 Can I just email the file to you and I'll let you click it and send it back to me?
• 00:51 And who really wants to do that?
• 00:53 Well, when you got these things in front of you, users can't help themselves.
• 00:56 They're like, ooh, something shiny, and they go and they poke it.
• 01:00 And at that point, you can see something changed, but maybe not what.
• 01:04 So let's try Burgers, boom, it filters the PivotTable and
• 01:07 you can see that it's actually setting the filters for us right away.
• 01:11 I can go and filter by Week.
• 01:13 I could go and clear the filter on Week.
• 01:15 And I can even if I want to start playing around with non contiguous items.
• 01:19 I can hold down my Ctrl key and I can actually start adding multiple things into
• 01:24 the filters as well, and it just keeps on adjusting the filters all the time I go.
• 01:28 I can even do this, I can left click and
• 01:30 drag to actually pull these up, or click on appetizers,
• 01:34 hold down my Shift key and click on food modifiers and get a nice big block.
• 01:39 In Excel 2016, the Excel team even went in and
• 01:41 added this really cool little thing here, which is a multi-select mode.
• 01:44 And this basically allows you to take selected items and
• 01:47 uncheck them, or check them to bring them in place.
• 01:51 So you can use your keyboard shortcuts or you can actually go and
• 01:53 use the multi-select.
• 01:56 The biggest thing you gotta teach your users is when they freak out,
• 01:59 because hey, I filtered your PivotTable and
• 02:01 I broke it, click the little filter with a red X to actually make them go away.
• 02:06 Now, I want to show you a little bit about how to format these things to make them
• 02:09 look a little bit better too.
• 02:10 One of the things I'm going to do,
• 02:12 I'm going to put some extra space across the top of my PivotTable.
• 02:15 And then I'm going to grab all three of these 1, 2, 3,
• 02:17 I'm holding down my Ctrl key to select them all.
• 02:20 I'm going to bring them up into the top corner here.
• 02:22 And now you'll notice that I've got them as high as where week will let me go.
• 02:25 But I really want to line them all up.
• 02:27 The last thing we want to see though is you trying to line each one up to get
• 02:31 them in the right place because you can do this.
• 02:33 Go to Slicer Tools, and in your Slicer Tools Options tab, you can do this.
• 02:41 We can go up to our little button here which allows us to go to align top and
• 02:46 it will snap them all in tight.
• 02:49 Now because I've had multiple sizes all selected, I haven't changed their heights,
• 02:52 I don't need to really worry about trying to get each height dialed in.
• 02:55 Although I could do that because the height of each slicer shows up in this
• 02:58 area and they're all consistent.
• 03:00 Because I've actually got this setup all together, I can just redrag my borders.
• 03:04 But if I didn't, I would click outside,
• 03:06 I'd look at this slicer, go to Slicer Tools Options, check the height.
• 03:10 Look at this one, make the height the same.
• 03:12 Now while I have all three of these selected again,
• 03:15 there is something that I do want you to check, and that's this.
• 03:19 Right click and go to Size and Properties.
• 03:21 You'll get a little task pane that comes out from the right hand side, and
• 03:25 buried under this little Properties guy right here is this option.
• 03:29 We do not want to move or size with cells.
• 03:32 What you can see is that right now, as I go and I actually click on an individual
• 03:36 item, all of these things keep moving around on me.
• 03:39 I don't want that.
• 03:40 I want to make sure that these things aren't shifting as the columns changed.
• 03:44 So go back, I'll select each one of these guys.
• 03:47 We'll go to, don't move or size with cells.
• 03:50 And also if I don't want to see these printed,
• 03:52 I would say unchecked that as well.
• 03:54 Now what you can see is as I slice these the column widths may change but
• 03:58 my slicers don't move, which is pretty cool.
• 04:02 Now we might want to do a little more formatting to these two.
• 04:04 Category for example, I might want to see more of my category items and
• 04:07 I'm just going to move these guys just a little bit to the right here for
• 04:11 a second, I just click my arrow and slid them sideways.
• 04:14 And I'm going to make category a little bit wider because I can't see them all.
• 04:17 Even though I've got a scroll bar here.
• 04:19 What I'm going to do, I'm going to select my slicer options, and
• 04:22 I'm going to dial up the number of columns to one, two.
• 04:26 Let's go with two we'll still leave a little bit of a scroll bar there.
• 04:29 Class is fine, but week, I'm going to dial this guy up so
• 04:33 that I can actually see all of my weekly items.
• 04:36 The other thing I want to point out is that we couldn't use week
• 04:40 in a filter field earlier in this course.
• 04:43 Well guess what, week is on a slicer, Class is on a slicer, so
• 04:46 in actual fact, I don't actually even need this Class field on my PivotTable at all.
• 04:50 I could go right click Show Field List, let's take Class, get rid of it.
• 04:55 Because guess what there it is.
• 04:56 And this is a nice thing about slicers too,
• 04:59 is that slicers actually can also coexist with things inside your PivotTable.
• 05:04 So this now is the default way that you want to slice up your documents.
• 05:07 Just makes it so much easier for your end-users.

Lesson notes are only available for subscribers.