GoSkills
Help Sign up Share
Back to course

Slicers

Compact player layout Large player layout

Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
199 KB
Slicers - Completed.xlsx
202.4 KB

Quick reference

Topic

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:
    • Excel 2013: PivotTable Tools > Analyze > Insert Slicer
    • Excel 2010: PivotTable Tools > Options > Insert Slicer
  • Select the field(s) for which you’d like to add slicers

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 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
Login to download
  • 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.

Built-in Filter Controls
5m:18s
Timelines
3m:48s
Share this lesson and earn rewards Facebook Twitter LinkedIn WhatsApp Email
Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2021 GoSkills Ltd. Skills for career advancement