GoSkills
Help Sign up Share
Back to course

Filtering

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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
17.8 KB
Filtering - Completed.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 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.

Sorting
04m:24s
Naming Ranges
05m:09s
Share this lesson and earn rewards

Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement