Locked lesson.
About this lesson
Learn to filter Excel data for specific words, dates, and apply multiple filters to a single data table.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Filtering.xlsx2.9 MB Filtering - Completed.xlsx
2.9 MB
Quick reference
Filtering
Filtering Excel data for specific words, dates, and applying multiple filters to a single data table.
When to use
Filtering can be very useful for drilling down into data, finding only records that have a specific relevance to the situation you are investigating.
Instructions
Preparing your data for filtering
- Make certain that your data is in tabular format
- It is essential that there be no blank rows or columns interrupting the data that you wanted sorted
- While not essential, it is certainly preferable to have a clear header row for your data
- Select the top row of your data, go to the Data tab, and choose Filter (this will add a row of drop-down arrows to your headers)
Filtering for specific words
- Click the drop down arrow of the column you wish to filter
- In the Search box, type the term you are looking for, then click OK
Filtering for specific dates
- Click the drop down arrow of the column you wish to filter
- Uncheck the Select all box to clear unwanted dates, then use the checkboxes to drill down to the records you wish
Multiple column filters
- You can add filters to multiple columns, drilling down to an ever decreasing amount of records
Clearing filters (3 ways)
- Click the filter icons on the column headers, then click the “Clear filter from” button in the menu
- Go to the Data tab and click the Clear icon on the Sort & Filter group
- Go to the Data tab and turn off filtering by clicking the Filter button, then add the filter back again
- 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.