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.xlsx16.7 KB Filtering - Extra Practice.xlsx
17.9 KB
Quick reference
Topic
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 In this video we're going to look at filtering which is the process of actually going through and
- 00:08 restricting the amount of records that we see to just see the ones we want in a specific table.
- 00:13 Something that's really, really important, absolutely critical when you're working with filtering is you need to have a good header row
- 00:20 that helps you at the top and it has to be a contiguous data range from
- 00:24 start to finish all the way to the bottom with no blank rows or columns in the middle.
- 00:28 The way you can test that is go to your header, press End and the down arrow.
- 00:33 And if that doesn't take you to the last record in the table you've got a blank row you need to actually go and fix that. So press End and up
- 00:39 arrow again and I'm going to go back up to my treatment date.
- 00:42 I'm just going to down arrow here so my rows come back where they're supposed to be.
- 00:46 Now I know that I've got a good solid table that I can work with. So to set up a filter what I'm going to do is I'm going to
- 00:52 go and select the headers here. And on the Data tab I'm going to go to Filter.
- 00:57 And what it does is puts these little drop down arrows across the top. This is kind of cool. I can go and
- 01:02 click on these and it gives me a nice big filter. Now
- 01:07 it's important to know that your data does not have to be in any order. As
- 01:11 you can see these are all out of order here, in dates, well actually it looks like the
- 01:14 date order is correct. But certainly the alphabetical is not. But we can still use this by just going and
- 01:19 typing into this little filter box here and seeing what happens.
- 01:24 So let's look for acute. You can see its come back with three different records. If we say ok it filters our table down
- 01:31 and you can see some indications of this in your table. Number one your
- 01:35 rows have turned blue in a certain area here instead of black so that shows you that something happened.
- 01:40 You also have noncontiguous numbers now showing. So these records have not gone away
- 01:45 they're just hidden.The other thing that we can see is next to our filter icon here there's a filter
- 01:50 picture right on the actual drop down arrow. That can be a little bit subtle of course but if you see that
- 01:56 you know you can actually go to it, uncheck it, click the select all button or the clear filter
- 02:02 and all your records will come back.
- 02:05 Now let's look at dates. Dates are pretty cool.
- 02:08 When we actually go into this we actually get a full list here, we can collapse this and expand it. So there's 2012 records, I can uncheck the 2012
- 02:16 records, I can look at just August and it'll filter my entire table down to August.
- 02:21 Or if I wanted to I could go back and also include March and April.
- 02:26 Say OK.
- 02:28 And we can see that I've got March, April and August in there as well. So that's pretty cool stuff, we can actually get those things in place.
- 02:36 I can clear the filters here. I can also use
- 02:39 some more advanced date filters if I want to. We can set it to Before or After, Betweens, if you're actually working on current data then these
- 02:48 Tomorrow, Today, Yesterday might actually help you as well or you can set to just get all dates in an August period or quarter
- 02:54 as well. We won't do that right now but some different options to work with.
- 02:59 Now what about filtering where one of two conditions is met? Let's go back to the DRG Definition
- 03:05 here for a second. And we're going to go into Text Filters and I'm going to go choose Contains.
- 03:11 So at this point I'm going to see if I can filter down records that contain heart,
- 03:17 or
- 03:19 and then I get to choose from my list again. I don't want equals, and down
- 03:23 the bottom of the list, here we go Contains . We have lots of options, does not contain, does not end with.
- 03:27 But I'm going to choose Contains. So I'm going to look for either heart or failure.
- 03:33 And see what comes back from my dataset here. Say OK.
- 03:37 There we go, I've got heart failures, I've got renal failures, pulmonary
- 03:41 edema and respiratory failures. All kinds of different, different types of things that have either the word heart or the word failure in them. So
- 03:48 some different things to see there. So that gives us the way to make choices between the two.
- 03:53 Go back and clear the filters from this one now. Now I'm going to start looking at multiple filters on
- 03:58 multiple columns. Let's go and look at the total payments and we'll just click on the
- 04:03 arrow here. And we'll say Number Filters let's choose greater than
- 04:10 and we're just going to choose 90,000.
- 04:13 So we'll leave it with that we won't bother with the next criteria.
- 04:17 So that's going to tell us that there's absolutely nothing there. Well that's no good
- 04:21 we want to have something a little bit, something to work with here.
- 04:25 Let's go back to
- 04:28 well let's say how about greater than 9000. See if we actually come up with anything there. There we go so we have some records now.
- 04:35 Why don't we also filter these guys down to say let's see what we have for August. So uncheck 2012 and click on August.
- 04:43 So now I just have the records that are greater than $9000 for August. You can see that I have two
- 04:49 filters active, there is a dropdown arrow here, or the filter icon on the arrow and the filter icon here.
- 04:55 Now one thing to be aware of is if you're working with huge datasets sometimes you can get lost because the filters go off the screen.If that ever
- 05:02 happens and you need to press the reset button. One of the ways you can do it you can press clear up here. But the other way
- 05:08 you can do it is you can highlight your rows with your filters and you can just uncheck the filter button
- 05:13 and at that point it will unfilter everything for you. If you turn them off
- 05:16 and then you can turn it on and you're starting right back over at square one.
Lesson notes are only available for subscribers.