Locked lesson.

Upgrade

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:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Filtering.xlsx
16.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
Login to download
  • 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.