🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

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
2.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
Login to download
  • 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.

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

Facebook 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