Locked lesson.
About this lesson
In this video we will explore setting up your PivotTable using best practices to allow one-click filtering and drill down, as well as linking filters to multiple PivotTables so you can keep everything in Sync
Exercise files
Download this lesson’s related exercise files.
PivotTable Filtering Tools & Techniques.xlsx1.8 MB PivotTable Filtering Tools & Techniques - Completed.xlsx
1.8 MB
Quick reference
PivotTable Filtering Tools & Techniques
An overview of modern filtering techniques for Pivot Tables.
When to use
When you want to look at only specific data in your Pivot Table.
Instructions
Modern filters such as Slicers and Timelines are much more user friendly than traditional filters
Inserting a Slicer:
- Go to Pivot Tables Tools tab --> Analyze --> Insert Slicer
- Select the field(s) for which you would like to create the slicer(s)
Formatting a Slicer:
- Select the Slicer and go to the Slicer Tools tab
- This provides access to change size, colours, alignment, buttons per row and more
Inserting a Timeline (a date specific slicer):
- Go to Pivot Tables Tools tab --> Analyze --> Insert Timeline
- Only fields formatted as a date will be provided in the selection list
Formatting a Timeline:
- Select the Timeline and go to the Timeline Tools tab
This provides access to change size, color, alignment, etc.)
Hints & tips
- Protect your Slicers and Timelines from being moved or resized when your Pivot Table refreshes:
- Right click on the Slicer or Timeline --> Size and Properties --> Properties section --> select option for Don’t Move or Size With Cells
- 00:01 The next thing I want to just review really
- 00:06 quickly here is filtering techniques because one of the really cool things
- 00:11 around your pivot table is the ability to actually filter it and drill down.
- 00:16 Now, there are certain things that we have in this Pivot table already that we can
- 00:21 use for that.
- 00:22 We've got this little arrows on here, where I can go and click on Class,
- 00:25 let's uncheck Expenses and we'll draw into revenues and that works quite nicely.
- 00:30 And I could then go and say hey, I'm gonna filter them and
- 00:33 I don't wanna see all these departments, I only wanna see 110, 120 and 150 and
- 00:37 this will work quite nicely to drill in.
- 00:40 The challenge I have with this is that if you ask a user to do this,
- 00:44 if you build them a pivot table and you send them these things and say, hey,
- 00:46 you just use your filtering ones.
- 00:47 They tend to freak out, they think they're gonna break stuff.
- 00:51 And that usually involves phoning me and being an anti social account,
- 00:55 I don't really wanna talk to people and tell them how to filter their pivot table.
- 00:59 So we gotta find a better way.
- 01:01 So, here's what I'm gonna do.
- 01:02 I'm gonna clear these filters.
- 01:03 I'm just gonna go clear and I'll go and clear the other filters as well.
- 01:07 And I wanna show you what modern filters look like inside Excel if you haven't
- 01:12 discovered these guys yet, we have two of them.
- 01:15 If you go to pivot table tools analyze, first one I wanna look at is the Slicer.
- 01:21 And the Slicer will pop up a list and
- 01:23 it would give you all of your different field names here.
- 01:26 So I can go and say, let's grab class and let's grab C group.
- 01:29 And we'll say OK and this brings up these boxes.
- 01:34 Now, what I'm going to do with these guys is, I'm going to go and
- 01:37 I'm going to move them around a little bit and resize these things a bit here.
- 01:41 And I'm going to move them over to the side.
- 01:43 And I'm going to try and get them, I'm going to hold down my control key,
- 01:46 select them both and narrow it up just a little bit here.
- 01:50 And move these guys around, all right, perfect.
- 01:52 So, these guys are in about the right place.
- 01:55 Now, they are boxes that float on the canvas, so, if you hold down your,
- 01:59 if you click on the little slicer here and you hold down your alt key and move it,
- 02:03 it will snap to grid, so that's a nice way of actually letting them
- 02:07 line up a little bit, so we can get them in the right spot and
- 02:09 they will all look good, so grab Group and snap those guys as well.
- 02:14 Maybe I'll make this a little bit bigger and then I can grab these guys and
- 02:18 make them a little bit wider as well.
- 02:19 So, hold down my Ctrl-key and grab both, and
- 02:22 we can make them a little bit bigger here, so we can see what's going on in them.
- 02:26 Now, the nice thing with a slicer versus these little filter arrows,
- 02:30 is when you put this in front of a user, they say, ooh something shiny and
- 02:34 they click it and at that point, it filters.
- 02:36 And these guys are not intimidating to actually use.
- 02:40 The one phone call you'll get is, hey, I drilled into or
- 02:42 I clicked on the button for revenues in green fees and
- 02:45 now I want to get back to everything else, how do I do that?
- 02:48 You say, well you've gotta click the little, red x's and at that point,
- 02:51 those things will go away and you'll be back to where you are and
- 02:54 that's usually about the only phone call that you get.
- 02:57 All though, you should probably also take the opportunity to show someone that
- 03:01 you can left-click and drag to select multiple items at once.
- 03:05 Or you can select non-contiguous items by clicking on one,
- 03:09 holding down the Ctrl key and clicking on some other ones.
- 03:12 Now if you happen to be on Excel 2016, there's also a multi-select mode
- 03:17 which will allow you click on that and then you can add the ones that you want.
- 03:22 And uncheck it and then, if you click on one, it'll drill into those.
- 03:26 So, some different ways to actually use your slicer.
- 03:29 This is modern filtering techniques right here.
- 03:32 Now, you can do all kinds of things with slicers.
- 03:34 There are certain things that I will do with these guys, always as well.
- 03:38 I'm going to grab both them for a second, right-click and
- 03:43 we're gonna go into size and properties.
- 03:46 Inside your properties, there is this little option here to don't move or
- 03:51 size with cells.
- 03:53 This is the equivalent of setting a pivot table to say, don't automatically expand.
- 03:59 So, if you do have slicers beside or
- 04:02 above your pivot table and it's automatically expanding and collapsing,
- 04:05 you're gonna want to make sure that these properties for don't move resize for
- 04:08 cells are set or else your slicers are gonna move all over the place as well.
- 04:12 For reference, there's some other things you should know about here too,
- 04:15 if these guys are not quite aligned perfectly, we can grab them and
- 04:20 under your slicer tools, on a line, you have the ability to line these guys left,
- 04:25 center, right, top, middle.
- 04:26 So you can snap them quite quickly to where you want them to be.
- 04:30 And then, if I grab both of them, I can move them around together.
- 04:33 Okay? So there's lots of different
- 04:35 tools in there.
- 04:36 If we want to put multiple columns on a slicer, we can grab the options and
- 04:41 up here, we can dial this guy up, so we've got two.
- 04:45 You can also go and change the colors on your slicer,
- 04:48 you've got all kinds of different styles that we can play around with.
- 04:51 Unfortunately these don't support live preview,
- 04:53 you have to click on them first to see them.
- 04:54 But they will work just fine.
- 04:57 I'm gonna click on the worksheet again cuz I wanna show you another slicer that's
- 04:59 also very, very awesome.
- 05:01 This came in in Excel 2013,
- 05:03 this is a new type of slicer, this one is called a timeline.
- 05:08 And the timeline slicer requires a date field, okay?
- 05:13 It has to have something formatted as a date.
- 05:15 So I'm gonna click on this and say,
- 05:16 okay, this is only one field in my entire workbook that looks like this.
- 05:21 The only challenge with the timeline slicer is it's very big.
- 05:24 You can't shorten it anymore than this or
- 05:26 else you'll lose the little timeline down the bottom, which is kind of a bummer.
- 05:30 But the way this guy works is awesome.
- 05:33 If I go and scroll all the way to the side here, I can click on May and
- 05:36 it'll drill in to May.
- 05:38 I can also expand it to take a look at a contiguous period here.
- 05:42 I can expand it to look at longer.
- 05:44 If I want to filter by years, I can change the drop down to say, show me years and
- 05:49 now I can look at just 2009 or
- 05:52 if I want to go all the way back to days, I can do that too.
- 05:56 I'm looking at January 2009 here.
- 05:58 I can look at a five day period in the middle.
- 06:01 Timelines are amazing.
- 06:02 They clear the same way, you can use multiple ones together.
- 06:06 They don't always play super nice if you've got slicers for year and your time
- 06:10 line working but at the end of the day, these things are a fantastic addition.
- 06:15 They're just a little bit bulky but a great way to go and slice your dates.
Lesson notes are only available for subscribers.