Locked lesson.
About this lesson
Using row and column 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.
Built-in Filter Controls.xlsx199 KB Built-in Filter Controls - Completed.xlsx
188.1 KB
Quick reference
Topic
Using row and column 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, or display only key values.
Instructions
Filtering Values
- Select an item you wish to filter in the rows area
- Click the drop down arrow in the first column
- Choose Value filters
- Choose the type of filter you wish to apply
- This should filter the values area of the Pivot
Filtering Labels
- Select an item you wish to filter in the rows/column area
- Click the drop down arrow for the row/column
- Choose Label filters
- Choose the type of filter you wish to apply
- This should filter the row/columns of the pivot (depending on which you chose)
Filtering Dates
- Select an item you wish to filter in the rows/column area
- Click the drop down arrow for the row/column
- Choose Date filters
- Choose the type of filter you wish to apply
- This should filter the row/columns of the pivot (depending on which you chose)
Removing Filters
- Select the filter icon on the pivot, or in the PivotTable field well
- Choose “Clear Filters from “x””
- 00:00 In this video, we're gonna look at a few different ways to filter,
- 00:06 and we're gonna focus on the values area of the pivot table
- 00:11 as well as the row and the column labels.
- 00:14 So, the first thing that I'd like to do is, I've got a nice little pivot that's
- 00:18 set up here with the category names and nested underneath that is the item names.
- 00:22 And the weeks across the top and our sales dollars amounts in the values areas.
- 00:27 And I'd like to filter this out to show all sales greater than $100.
- 00:31 So what I'm gonna do is, I'm gonna come over and
- 00:34 I'm gonna click on one of my products over on the side here.
- 00:38 I'm gonna go to the filter arrow on Items, and
- 00:42 I'm gonna choose Value Filters, and I'm gonna choose Greater Than Or Equal To.
- 00:46 And in this particular case, it says, all right.
- 00:49 Let's see which one, Sales $, which is from the values area of the pivot table,
- 00:53 is greater than or equal to 100.
- 00:55 And it should filter to only pull up items where the grand total is greater than or
- 01:01 equal to $100.
- 01:02 So that works out nicely, and
- 01:04 you can see that my subset of data is trimmed down quite a bit.
- 01:09 You'll also notice when I do that,
- 01:10 that I've got an indicator on my little drop-down.
- 01:13 Instead of just the arrow like I have for Week, I've actually got a little filter
- 01:17 icon, and that tells me that something is being filtered there.
- 01:19 So if I click on that, and I wanna undo it, well, hang on a second.
- 01:24 Let's go back into one of the sales items because that's what I'm
- 01:28 actually working with.
- 01:29 Now we can filter it and say Clear Filters From Item Name.
- 01:32 The reason why it didn't do it before is because I'd actually selected a category,
- 01:36 and a category is not filtered.
- 01:39 You can also get an indicator of what is filtered
- 01:42 by looking at your pivot table field well.
- 01:44 And you can see here that Class and Item Name have been filtered, so
- 01:47 if I clicked on this guy here, I can clear the filter from Item Name, and
- 01:51 that'll put everything back to the way it was.
- 01:54 I find this is a little bit hard to work with in compact view sometimes.
- 01:57 If we do flip the pivot table tool design report layout into tabular form,
- 02:04 you'll notice that this would've been a lot easier to figure out because my
- 02:08 greater than filter was actually over here.
- 02:11 And at this point,
- 02:12 I can now see that it's filtered on this column but not this column.
- 02:16 Sometimes, this particular view is easier to build your pivot table if you're trying
- 02:20 to get into some more finer points on this stuff.
- 02:24 So I'm gonna clear that again.
- 02:26 Now, what about labels?
- 02:28 What if I wanted to filter by the name here?
- 02:31 Anything in the row areas or the columns are always text,
- 02:35 and that's something to, well text or dates.
- 02:39 Those or the two things generally that we see.
- 02:41 So if I want to filter for a specific product, let's go and
- 02:45 look at a labels filter, and I'm going to look for Contains.
- 02:48 And just for fun, you'll notice down the bottom here that I have the ability
- 02:52 to actually use some wild cards.
- 02:54 So I'm gonna look for anything that contains star,
- 02:57 so anything slash half star.
- 03:01 Let's see what ends up coming up here.
- 03:04 You'll notice that immediately all of my beers are all gone.
- 03:08 I'm just down to wine, and
- 03:10 you can see that i've picked up the half of the JT Merlot, and
- 03:14 you can see that it goes half space all the way down to here and then half L.
- 03:18 And this one's got Cracked Frank's Merlot 1/2.
- 03:21 So anywhere where it has a half in there, it's pulled all those kinda things up, so
- 03:25 that's kind of a neat way for being able to deal with that.
- 03:28 Once again, I wanna get rid of that filter.
- 03:30 I can just come back here and say clear the filter from the item name.
- 03:35 Now, another real quick one here, I'm gonna pull Category off of my pivot table.
- 03:40 I'm gonna pull Item Name off my pivot table.
- 03:42 And I'm gonna throw the POSChitDate on here.
- 03:46 We can see our sales by day.
- 03:47 I probably don't really need week number on this either.
- 03:51 At this point, I could go and filter by date.
- 03:54 So if I click this drop-down arrow,
- 03:56 you'll notice that we have a little contextual change here.
- 03:59 It gives us date filters.
- 04:00 And I can go and choose to filter for some dates between, or
- 04:03 if I'm using current data, next week, this week, last week.
- 04:06 All these kind of different areas,
- 04:08 all dates in the period of November or January.
- 04:11 What I'm going to do is I'm going to pick up some dates between.
- 04:15 I'm going to filter it and say, you know what,
- 04:18 give me the days between January 15th, 2013 and January 22nd, 2013.
- 04:23 Maybe I don't have a week field set up for some reason.
- 04:26 If I say OK, you'll notice that it filters the entire pivot table down.
- 04:31 And you can see that my ChitDat over here has been filtered as well as my Class
- 04:36 up from my report filter as well.
- 04:37 So lots of different ways to filter based on dates, based on labels, and
- 04:42 based on values in the pivot table itself.
- 04:44 Highly encourage you to check those out.
- 04:47 Again, even though we filtered by labels for dates here,
- 04:50 we can still monkey with our values areas and
- 04:54 filter now for, let's say, sales or days that are less than $100.
- 04:59 Value filter less than $100, and you'll see that we cut
- 05:04 down even some more records there, so that replaced the date filter for me actually.
- 05:08 But lots of different ways to filter in a pivot table.
Lesson notes are only available for subscribers.