PivotTable Filtering Tools & Techniques

Quick reference

• 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.