🄳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Slicers

Compact player layout Large player layout

Locked lesson.

Upgrade

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

Built-in Filter Controls
05m:18s
Timelines
03m: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

© 2023 GoSkills Ltd. Skills for career advancement