Filtering

Quick reference

• 00:04 We're now gonna look at filtering in Excel, which is a super useful tool for
• 00:10 cutting down a massive list to look at smaller subsets of the data.
• 00:15 So, right now I've got a list of transactions, It's only about 20 rolls
• 00:18 long, but it scrolls off the screen so you can't see it all.
• 00:21 And you like to filter through and come up with relevant records.
• 00:25 Now before we start, like working with sorting,
• 00:28 this has some key characteristics that are very important to us.
• 00:32 Number one, the data is in a tabular format.
• 00:35 So, we've got lots of rows and a few columns of data.
• 00:38 Notice that once again we have a good descriptive header row again,
• 00:43 not essential but very useful.
• 00:46 Another critical part, no blank rows or blank columns in the data and
• 00:51 again, consistent data types all the way down the column so,
• 00:55 with that set up it's perfectly in good shape right now.
• 00:58 The B filtered so, what I'm gonna do is I'm gonna click inside my first row,
• 01:02 my header row and I'm gonna go to the Data tab and I'm gonna chose Filter.
• 01:07 And you'll notice it puts these little drop down arrows on
• 01:10 everyone of the columns.
• 01:12 Now, this is pretty useful because we can actually get a few different things.
• 01:17 Right off the bar, if I click one of these filter icons it comes up with a list, and
• 01:22 it's actually a sorted list of unique values so
• 01:24 I can see that in this entire column whether it's 20 rows or
• 01:28 20,000 rows, there's only two unique entries, beach bar and lobby bar.
• 01:33 If I go and take a look at the card type over here, you can see that I've got Amex,
• 01:37 Mastercard, and Visa.
• 01:38 I also, have the ability, if I want to here, to Sort A to Z, Z to A,
• 01:42 or even by color or even a custom sort.
• 01:45 So that's pretty cool.
• 01:47 But what if I actually want to filter down my locations?
• 01:50 Maybe I want to pick up just Visa transactions.
• 01:53 Well I can click on this filter, and I can uncheck the select all box, and
• 01:58 I can check Visa and say okay.
• 02:01 And just like that, it filters the entire list down.
• 02:04 Now key thing to recognize here, it didn't delete any data.
• 02:07 You can actually see, based on the row numbers here,
• 02:09 it's essentially hidden those rows.
• 02:12 And if I want to bring them back, all I need to do is click the filter and
• 02:16 say Select All, or Clear Filter From "Card Type," and
• 02:20 it will bring all of those transactions back.
• 02:22 What if I don't know the exact term, or the list is really long?
• 02:26 Well I can actually do this.
• 02:27 I can start typing B-e-a-c and you can see that it's actually narrowing this down,
• 02:33 pulling back selections that actually have the letters that I've typed.
• 02:37 So this is pretty cool, it's like her typing.
• 02:40 Now I click the X to clear that, or I could say hey this is OK.
• 02:43 I want to keep my Beach Bar only, so that's kinda neat.
• 02:47 Can I use that to go even deeper?
• 02:50 Well, sure.
• 02:51 What if I want to go back to the Card Type and say,
• 02:53 give me only the card types that contain the letter E?
• 02:56 And you'll notice that it says, Amex and Master Card fit that list but
• 03:01 Visa certainly does not.
• 03:02 So, what will happen now if I click OK?
• 03:05 Because I've already filtered Beach Bar.
• 03:08 Well, it actually applies a more complex filter.
• 03:11 So it actually narrows this record set down even more, which is pretty cool.
• 03:16 So this is nice, we have the ability to actually really drill down into our
• 03:19 data and get to a smaller subset.
• 03:21 What if I want to remove some of these filters?
• 03:24 Well, I can certainly go back and say, clear filters from Card Type and
• 03:28 then clear filters from Location.
• 03:30 But what if you've got 90 columns and you can't tell which ones are done?
• 03:34 Well, there's an easier way to do this.
• 03:36 We can actually select the entire row and we can come up to our filter here, and
• 03:40 we can choose clear or, we can even turn the filter off and turn it back on again.
• 03:46 Either of those will actually clear the filter out.
• 03:49 Do you have the ability to get to even more complex filters?
• 03:52 Well, of course you do.
• 03:53 You can go into your location here.
• 03:55 We can go to Text Filters and we have the ability to say,
• 03:58 equals or a few other options that are actually in the list here.
• 04:02 Quite a few, actually.
• 04:03 And we can even set up and add an or clause to go with them if we need to so,
• 04:07 that's pretty useful.
• 04:09 Now the final thing that I wanna show you here before we move on from this topic,
• 04:14 is I wanna show you what happens when you're working with dates.
• 04:17 Because when you're working with dates, we have a lot of them here from June 30th,
• 04:21 2018 we can actually get a specific set of date filters if these are dates.
• 04:26 Notice that in the little drop down window here, we can make this just a little bit
• 04:30 bigger, you can see that it actually will allow me to filter very easily by year,
• 04:34 by month or by day.
• 04:36 So if I were to come back and say, you know what?
• 04:38 This transaction here was actually for 2019,
• 04:41 you'll notice that I can actually filter this.
• 04:45 Now and I get the ability to drill into 2019's June,
• 04:49 2018 I can go all the way down to the day level so, this is quite useful.
• 04:52 In addition I have the ability to filter to things like next month, this week,
• 04:58 always based on whatever today's current date is no matter what the data is.
• 05:02 So there is some useful filtering techniques that will help you slice down
• 05:06 into your data to be able to read it a little easier.

Lesson notes are only available for subscribers.