Filtering

Quick reference

• 00:04 Another very useful skill when you're working with Excel is the ability to
• 00:08 filter a dataset down to restrict it,
• 00:10 so you can look at a smaller subset of records.
• 00:13 Now, in order to do this,
• 00:15 the most important thing is, is that your data has to be in good form.
• 00:20 A header row is not required but highly, highly recommended.
• 00:24 The requirements though, your data needs to be in a tabular format.
• 00:29 Now that can be tall and narrow, short and wide, or anything in between,
• 00:33 but you want to have a consistent set of data going down, with no blank rows and
• 00:38 no blank columns.
• 00:39 That's the most critical part, no blank rows, no blank columns.
• 00:43 Now once we have that, what I'm going to do, is I'm going to go and select my
• 00:47 headers here, and I'm going to go to the Data tab and click the Filter button.
• 00:53 And what you'll see is it adds all of these little drop down arrows to each of
• 00:56 the columns.
• 00:57 And what I can do with this now is I can come back and say, hey, you know what,
• 01:01 I'd like to see just the records for example, for the beach bar.
• 01:04 So I'll just uncheck lobby bar right here, and boom, there we go,
• 01:08 there's all the beach bar records.
• 01:10 Now if I want to bring that back, I can click Clear Filter From location,
• 01:14 this is one way to remove a filter from my dataset.
• 01:19 I'm going to go and filter now on my card type.
• 01:21 And we're going to go uncheck Select All and grab just Visa.
• 01:25 And I'm going to say OK.
• 01:27 All right, now some of that's important to recognize here and
• 01:31 you've already seen this,
• 01:32 although I didn't call it out is that the data is not gone, it's just hidden.
• 01:37 And we can actually see that because in our row numbers, we go from 6 to 8, so
• 01:40 row 7 is hidden.
• 01:42 We go from 9 to 12, so 10 and 11 are hidden, they're not relevant,
• 01:45 they're not visa records.
• 01:47 Now the nice thing is, is that every time a filter has been applied to this table,
• 01:51 you will see a little icon that actually shows that it's been filtered.
• 01:55 See that little icon there that looks like a little filter that's on that button?
• 01:58 The other columns don't have that because they're not filtered at this point.
• 02:02 Now I can go and modify this filter, and you know what I'm going to do?
• 02:06 I'm going to go back here and I'm actually going to check on something on this one.
• 02:09 I'm going to start typing for
• 02:11 something to see if I can actually find a specific word.
• 02:14 So I'm going to go with M, and you'll notice that when I do that,
• 02:18 it actually filters visa out of the list, and only shows me AmEx and MasterCard.
• 02:22 So now I can go and type in A and it reduces it to MasterCard.
• 02:26 So that's a nice little way to do a filter to try and
• 02:29 figure out what items I have in a larger list.
• 02:32 I spelt master wrong, so everything disappears.
• 02:35 I'm just going to go and get that back, and there we are.
• 02:38 All right, can you set up multiple filters on your table?
• 02:43 Well, this column is filtered, let's go look at lobby bar.
• 02:46 Hey, look at that, let's go uncheck this one, we're going to filter for
• 02:49 just the beach bar.
• 02:49 So now we have MasterCard with just Beach Bar records, so that's pretty cool.
• 02:55 What if I want to get rid of these filters?
• 02:57 Is there a way to get rid of all of them?
• 02:59 Well, yeah, I can highlight this entire row here, and I can choose clear, and
• 03:04 that will get rid of all the filters as well.
• 03:07 Filters are also somewhat specific to the data type that is in the column as long as
• 03:12 the data type is consistent.
• 03:14 Notice that when I'm filtering card types, I get text filters for equals,
• 03:17 doesn't equal, begins with, and things like that.
• 03:21 When I move to amount, I get filters that are also equals, but I get greater than,
• 03:26 less than, or equal to, or between top ten values.
• 03:29 And if I go to dates, I get specific date filters as well for
• 03:33 all kinds of different things.
• 03:36 Notice here that I can actually filter these things down and say,
• 03:39 I don't want all of the days, I'm really only interested in the 8h, 9th, 10th, and
• 03:43 11th of these months.
• 03:45 We go and say, OK.
• 03:46 And this is the records we get, and now we can drill it down a little bit more, okay?
• 03:51 Worst case, you get a massive column.
• 03:53 You got all these columns all over the place, and
• 03:55 you can't tell where your filter icons are and you get completely lost in your data.
• 04:00 Select the entire row, turn the filter off,
• 04:03 turn the filter back on again, and that will actually reset the filter as well.
• 04:08 Same as what the clear button does,
• 04:10 just a different way of actually clearing your filters there as well.

Lesson notes are only available for subscribers.