Filtering

Quick reference

• 00:04 We're now going to look at filtering data in Excel.
• 00:07 This is allowing us to grab a big list of transactions here, and
• 00:12 actually drill into the ones that are most relevant to what we wanna see.
• 00:16 Now again, like with sorting, there are some key characteristics about our data
• 00:20 that we need to make sure are in existence before we start.
• 00:24 Number one, our data needs to be in a tabular format.
• 00:27 So it needs to look like a table with lots of columns and lots of rows of data.
• 00:31 It's essential that there be no blank rows or
• 00:34 columns interrupting the data that we want filtered, that's really key.
• 00:38 Okay, no blank rows, no blank columns.
• 00:41 Again, while it's not entirely essential,
• 00:43 it's certainly preferable to have a clear header row for
• 00:46 the data that we have because that's what we can use to actually drive our filters.
• 00:50 So we definitely want to have that if we possibly can.
• 00:54 Once we've got it, what we're gonna do, is we're gonna go and
• 00:57 select the header row of our data.
• 01:00 We're gonna go to the data tab, and we'll click filter.
• 01:04 And what this will do, is it will add some drop down arrows on each of the columns.
• 01:09 Now filters are really cool because we can actually go and
• 01:13 click on these little drop-downs and
• 01:14 we can number one get a sorted list of the unique values that are in them.
• 01:19 So right off the bat that tells us we've got AMEX, MasterCard, and
• 01:22 Visa showing up here.
• 01:24 Now, if I want to go and take a look at something, I can uncheck the Select All
• 01:29 box, and I could check show me just the transactions for Visa, and say okay.
• 01:34 And it's gonna filter the list down to show me just the Visa transactions.
• 01:37 This is kinda nice.
• 01:39 It actually hides the rows.
• 01:40 It doesn't delete them, okay?
• 01:41 You can see that we've gone from row 7 to 10.
• 01:44 And you'll also notice that the filter icon now has a little filter showing it as
• 01:49 well as the arrow.
• 01:50 That means that there's an active filter here.
• 01:52 So we could check this and say clear the filter and
• 01:56 it'll actually bring all of our records back.
• 01:57 They don't go away, they're just hidden, so that's kind of nice.
• 02:00 If I wanted to filter for a specific word, maybe on location here I
• 02:05 wanted to start filtering for anything that had beach in it.
• 02:09 I could say b e a c h, and you'll notice that it would filter down my list to say
• 02:14 the only thing that matches is beach bar, would you like that?
• 02:18 And I can say, sure.
• 02:19 Okay, and it'll filter down to beach bar.
• 02:24 This is really useful in the case where we have many
• 02:27 values that are actually repeating, so in a certain case if I wanted to filter for
• 02:32 a specific letter only, I could just type in an E.
• 02:36 And you'll see that it tells me that both Amex and Master Card have E's in them but
• 02:40 Visa obviously did not.
• 02:43 I also have much more granular filters that I can use.
• 02:46 Equals, doesn't equal, begins with or contains.
• 02:49 So, if I were filtering for specific terms that I was actually looking for.
• 02:54 Maybe with different data I could filter for
• 02:57 something that contains a specific term.
• 03:00 So, and I can actually set up two of them, an And or an Or here.
• 03:04 So I've got a little bit of ability to actually go and
• 03:06 sort that data in different ways.
• 03:09 Now what if we apply multiple filters?
• 03:12 What if we go and say, you know what I want to see just Visa for right now?
• 03:16 Or maybe VISA and MasterCard, let's do that.
• 03:18 Now I want to say show me just the transactions from the lobby bar.
• 03:24 Can we do this?
• 03:26 And we absolutely can.
• 03:27 These filters layer on top of each other to give us a more complex filter,
• 03:31 and that's really really handy.
• 03:33 Now the challenge here is that sometimes, when we've got lots and lots of columns,
• 03:38 we can get lost in our filters.
• 03:40 So, if we need to reset them, I mean, obviously, we can go back, and
• 03:44 we can uncheck and clear each filter.
• 03:46 But if you have more columns that are on the page, and
• 03:48 you don't have 100% confidence that you've actually cleared all those things.
• 03:53 Because maybe there's 40 columns of data that you're working with and
• 03:56 maybe one is filtered, but you don't know for sure.
• 03:59 One of the easiest ways to deal with this.
• 04:01 We can actually go and select the column header, and we can press Clear.
• 04:06 And that will clear all the filters that are on there.
• 04:10 The other option that we have, if we have our data filtered in some way,
• 04:13 let me just go and filter something here.
• 04:16 We could go and, Uncheck the filter and turn it off.
• 04:21 And then turn it back on and it comes back reset.
• 04:24 The last thing I wanna show you is when you're filtering dates in Excel.
• 04:29 It actually gives us a date hierarchy in these things.
• 04:32 So we can filter for all dates in 2015 or all dates in June.
• 04:36 And it drills us all the way down into the 13th and the 14th.
• 04:40 If I have multiple dates here, I could actually collapse these things down so I'm
• 04:43 not looking at so much stuff, but it gives me some real nice ways to get in there.
• 04:48 And of course, we could also go through, and
• 04:50 actually set up custom filters in here as well that say give me all the records for
• 04:54 this month or all the records for next quarter last quarter.
• 04:58 These work on a standard calendar year round of course.
• 05:00 But lots of options there that are specific to dates as well.
• 05:03 So that's filtering in a nutshell in Excel.

Lesson notes are only available for subscribers.