Built-in Filter Controls

Quick reference

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