Report Filters

Quick reference

• 00:05 In this video, let's look at filtering, or cutting down our pivot table so
• 00:09 we don't see quite as many records.
• 00:12 Now, we have a rather large pivot table
• 00:15 here that spans all the way down to row 217.
• 00:18 This is every single item that's in our sales listing,
• 00:21 as well as the different weeks, and how much sales dollars is actually sold.
• 00:26 But ideally, I'd like to break this down a little bit more so
• 00:29 that I can actually look at it, and pick between the categories and
• 00:33 the classes that I actually wanna see.
• 00:35 For that, we have this top area of our pivot table,
• 00:38 or top left in you're looking in different view, called filters.
• 00:42 Now, what I'm going to do is I'm going to drag Class on top of that area.
• 00:46 What will happen is immediately above of our pivot table,
• 00:50 it will put in a new area here.
• 00:53 And in here it says Class All.
• 00:55 And you'll notice if I click the little drop down arrow,
• 00:58 I have the ability to pick just alcohol.
• 01:01 And if I do that and say, okay.
• 01:03 It'll filter the table up to only show me the alcohol items.
• 01:07 And then, I could go back in here again, click on the little arrow, click Food.
• 01:11 And you'll notice the whole pivot table will update now
• 01:14 to only show me the food items.
• 01:17 And of course, I also have the ability to select multiple items,
• 01:20 which would allow me to check all of them again, as well.
• 01:24 Now, what if I wanted another report filter?
• 01:28 Maybe I wanted to filter it down a little bit more.
• 01:30 I'm gonna drill into say, alcohol.
• 01:32 So I'll uncheck the all box and
• 01:34 check alcohol because I'm now on the select multiple items.
• 01:38 But now, I wanna drill in and I only want to look at wine products.
• 01:42 Well, my category has that, so I'm going to drag that and
• 01:45 put another filter in here.
• 01:46 And watch what happens to the existing one that is in row two.
• 01:50 It just gets bumped up and if we add more to them,
• 01:52 it will now start bumping th pivot table down the sheet, okay?
• 01:55 So it moves it around a little bit.
• 01:57 Category is also applied currently as all, but uncheck it.
• 02:00 You'll notice that I can select multiple items, that's great.
• 02:04 And I'm going to go and uncheck this, and let's go look for, you know what?
• 02:08 I don't know how far down the list it is, so
• 02:10 I'm just gonna type wine into the filter.
• 02:12 There we go.
• 02:14 And it will allow me to add it to my filter, and
• 02:18 now I have Just the items that are wine related in here.
• 02:23 Which is pretty cool.
• 02:25 Now, I could go back and I could see.
• 02:26 You know what? 'd like to see everybody again.
• 02:29 Let's check all.
• 02:30 And then, maybe let's try and find beer.
• 02:32 And you'll notice that when I do that, and fortunately I can make
• 02:36 this window bigger by clicking in the bottom left or right corner and dragging.
• 02:40 You'll notice that it actually picks up all of the beers that are in here,
• 02:44 which is perfect.
• 02:45 Because now, I can actually see what's going on with all of them.
• 02:48 So that's actually pretty cool.
• 02:51 That's how actually start working with the report filter to filter different
• 02:54 pieces out.
• 02:55 Now, what if you wanted to have these guys,
• 02:57 instead of being stacked on top of each other like this?
• 03:00 If you wanted your class here, and then your category over here.
• 03:05 Can we do that?
• 03:06 Well, of course we can.
• 03:07 We're gonna right click, we're gonna choose PivotTable options.
• 03:12 And we're gonna go to the layout and format tab.
• 03:14 And you'll notice that it says, display fields in the report filter area, down and
• 03:18 then over.
• 03:19 And the report filter fields per column is left right now at a default of zero.
• 03:24 If I were to change this and
• 03:25 say over then down, it will actually move across and then come down.
• 03:31 So watch this now.
• 03:34 We get a class here and our category over here, so
• 03:37 just a little bit more compact view from what we have.
• 03:42 If I were to go back and modify this again,
• 03:46 I could say that I wanna have one report filter field per row, or two per row.
• 03:51 And at that point in time, if I said one, it'll push these guys here,
• 03:54 because this is only one per row.
• 03:56 And then we're gonna move to the next row.
• 03:58 If I had six or seven different things I was filtering, I could go back and
• 04:02 I could set this to say, give me two fields per row.
• 04:06 And it will put one, one here, and then move the next one down as well and across.
• 04:11 And we would end up with three of them, if we had six altogether.
• 04:14 So in this particular case, I can set it to two.
• 04:17 Or if we leave it a zero, it's the default and
• 04:19 it just goes the width of the pivot table and then wraps.
• 04:22 But that's how we start setting up our report filters, and
• 04:26 how we can drive and filter our pivot table with them.

Lesson notes are only available for subscribers.