Locked lesson.
About this lesson
Slicers are user-friendly filters for PivotTables.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Slicers.xlsx206.5 KB Slicers - Completed.xlsx
210.6 KB
Quick reference
Slicers
Slicers: User friendly filters for PivotTables.
Where/when to use the technique
When you want your users to engage with your reports and not be afraid to filter them.
Instructions
Creating slicers
- Select a cell in your PivotTable
- Go to PivotTable Tools > Options > Insert Slicer
- Right-click the field in the PivotTable field list > Insert Slicer
Arranging slicers
- Select all the slicers
- Go to Slicer Tools > Options > Align > Align Top
- With them still selected, drag the bottom border of one up to shorten it (does them all)
- Click in the worksheet
Configuring slicers
To not move when a Pivot Table is refreshed
- Right-click the slicer > Properties > Properties
- Select “Don’t move or size with cells”
To not print
- Right-click the slicer > Properties > Properties
- Uncheck the “Print Object” checkmark
Adding columns
- Increase the column count at Slicer Tools > Options > Buttons > Columns
Styles
- Slicers have styles that can be customized like a PivotTable
Using slicers
- Click any of the bubbles to filter your PivotTable
- Hold down CTRL and click to select multiple items
- Click the red x on the filter icon to clear them
- 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.