Built-in Filter Controls

Quick reference

• 00:04 We're going to continue looking at filters on a pivot table.
• 00:07 But this time we're going to focus on filters that are actually applied
• 00:10 right on the actual pivot table themselves.
• 00:12 So you'll notice that we have a filter field here for week,
• 00:14 and you'll notice we have label and we have value filters in this area,
• 00:18 as well as the ability to use the filter pane to search things down.
• 00:22 Also, on our items, we have the same thing over here as well so
• 00:26 that we can actually play around with these things.
• 00:28 What I'd like to do right now is I'd like to filter
• 00:30 my pivot table to say show me only sales items that are greater than $100.
• 00:34 To do that, what we're going to do is we're going to go and
• 00:38 grab one of our sales items and it really doesn't matter which one.
• 00:42 This is actually the sales item field, which we can see when we go in and
• 00:45 take a look at the Field List.
• 00:46 Here it is, Item Name, so I'm going to start, Bag of Chips,
• 00:50 although it really doesn't matter which one I use.
• 00:52 What I'm going to do is I'm now going to go click the little drop-down to go
• 00:55 into a filter.
• 00:56 Now, I could set up Label Filters for text-based stuff, but
• 00:59 what I'm interested in is a values filter for sales items that are greater than or
• 01:03 equal to $100.
• 01:06 Now the thing I want you to notice here,
• 01:08 you can change all of the different mathematical operations.
• 01:11 But in this case the only thing a value filter will let you do is set up against
• 01:15 the fields that are in the bottom right hand corner of your pivot table frame.
• 01:18 In this case, the only one we have is Sales $.
• 01:21 When I click OK it now filters the entire list down to say here's all your sales
• 01:25 items where the grand total is greater than $100.
• 01:28 That's pretty darn cool.
• 01:30 But now I say, all right, well that's cool but you know what?
• 01:32 I want to change this.
• 01:33 I can see that there's a nice little filter icon that's on here,
• 01:35 different than the week and the class which haven't been filtered.
• 01:38 I'd like to clear this filter.
• 01:40 So I'm going to go back to it.
• 01:42 And you'll notice that clear filter is grayed out, go what is this?
• 01:45 You can only set a filter and never remove it?
• 01:46 That's not right.
• 01:48 You'd also think that you would actually have a checkbox next to value filter.
• 01:51 And as it turns out,
• 01:52 this is one of the really confusing things about using these filter arrows.
• 01:56 Our pivot table is in compact form.
• 01:59 And the challenge is we have both category and item name in the row fields, and
• 02:03 what have I selected?
• 02:05 A category.
• 02:06 What did I apply my filter to?
• 02:07 An item.
• 02:08 If I go back and select the item, you'll now notice
• 02:12 that I can clear the filter here and my value filter is checked.
• 02:16 This is one of the things that I think is terrifying around working with this
• 02:19 interface and why I often flip my pivot tables
• 02:22 directly into tabular form before I release them to my users.
• 02:26 Reason being, now look, Category has no filter, Item Name certainly does.
• 02:32 I have actually selected a category, but if I even go over and select my
• 02:35 item name right now, notice that I can now clear the filter from that field.
• 02:39 So this is a lot easier to use when you can see where the filters have truly been
• 02:43 applied.
• 02:44 Now what if I wanted to filter based on text in here?
• 02:48 I, somewhere in this data set, have a whole bunch of items that have the term
• 02:51 one slash two in them, a half a unit of something.
• 02:55 I'd like to filter to find all of those, but they exist in both food and in liquor.
• 02:59 So how do I locate them?
• 03:00 I could click on my little filter for Item Name, and
• 03:03 I could easily go into Label Filters, Contains.
• 03:06 This would allow me to actually search for the term contains 1/2.
• 03:10 But you'll notice that it also has some cool stuff around here about wildcards,
• 03:15 using the asterisks to represents any series of characters.
• 03:19 So this would totally work, but I'm going to cancel this and
• 03:22 show you that we can actually do it right from here with the term *1/2*.
• 03:26 And at that point it will bring up a list of all of my items that contain the term
• 03:31 1/2, including Pizza 1/2 Dz Wings, 1/2 Sandwich, and
• 03:35 a whole bunch of 1/2L of wine.
• 03:37 So that's pretty nice stuff.
• 03:40 There's one more filter I want to show you.
• 03:42 I'm going to clear this out.
• 03:43 So we're going to go back to Clear, and
• 03:45 I'm going to take a couple of fields off the pivot table.
• 03:47 We're going to get rid of Category, and we're going to get rid of Item Name.
• 03:51 And instead, I'm actually going to slide on the POSChitDate.
• 03:54 And you'll see that this gives us a whole bunch of sales.
• 03:58 Now it's all broken down by week here and everything else, which is great.
• 04:01 I can take that off the pivot table if I wanted to.
• 04:03 But what I'd like to do is I'd like to actually filter this down to only
• 04:07 a specific range of dates.
• 04:09 So what I'll do is I'll go and filter, and I'm going to say, hey,
• 04:12 now we've got value filters because dates are numbers.
• 04:15 And we don't have text based filters anymore, label filters,
• 04:18 we now have date filters.
• 04:20 Notice I can get filters for this week or this month.
• 04:24 Now these are sensitive to what the actual time is, so I'm not going to
• 04:28 use that because in later dates it's not going to work for this video.
• 04:32 But what I am going to to do is, I'm going to choose a specific set of dates.
• 04:35 We're going to go between.
• 04:38 And from the calendar I'll dial myself back over here to January,
• 04:41 and I'm going to go dates between January 1st, and
• 04:44 I'll dial this one back as well, we'll go up to January 5th.
• 04:48 And what you'll see is when I go and click on this,
• 04:50 it's going to cut the whole pivot table down to just that date range.
• 04:54 This is pretty useful stuff for
• 04:55 being able to drill into a specific area of your pivot table as well.
• 04:59 So that's several different ways to go and
• 05:02 slice down our pivot tables based on dates, based on text, based on amounts.

Lesson notes are only available for subscribers.