Locked lesson.
About this lesson
Using Report Filters to narrow down results in a PivotTable.
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.
Report Filters.xlsx197.2 KB Report Filters - Completed.xlsx
188.7 KB
Quick reference
Topic
Using Report Filters to narrow down results in a PivotTable.
Where/when to use the technique
When you wish to give the user the ability to slice into their data and show a subset of the master list.
Instructions
Creating Report Filters
- Drag the desired field form the fields list into the Filters area of the PivotTable
Multi-Selection
Enabling Multi-Selection
- Click the drop down filter arrow for a field
- Check the box next to “Select Multiple Items”
Disabling Multi-Selection
- Click the drop down filter arrow for a field
- Uncheck the box next to “Select Multiple Items”
Clearing Filters
- Click the drop down filter arrow
- Choose (or check the box next to) “All”
Arranging Filters Horizontally
- Select a cell in the Pivot
- Right Click > PivotTable Options > Layout & Format
- Change the “Display fields in report filter area:” setting to Over then Down
- Toggle the “Report filter fields per column” as needed to control how many rows are displayed in each column
- 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.