🥳 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.

Exercise files

Download this lesson’s related exercise files.

Filtering.xlsx
17.6 KB
Filtering - Completed.xlsx
17.9 KB

Quick reference

Topic

Filtering.

Description

Filtering Excel data for specific words, dates, and applying multiple filters to a single data table.

Where/when to use the technique

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 in Excel and
  • 00:08 what filtering is, is it's the ability to take a list of data or
  • 00:12 transactions and drill into specific aspects that we actually wanna see.
  • 00:17 So we can get rid of a lot of extra noise and
  • 00:20 really focus on things that are relevant to us.
  • 00:23 To get started with filtering like sorting and also other powerful
  • 00:28 tools inside excel, we need to work with what's called a contiguous list of data.
  • 00:32 Which basically means that we need our data preferably in tabular form
  • 00:37 with a nice set of headers across it.
  • 00:39 And it's really,
  • 00:40 really important with this that we don't have any blank rows or headers.
  • 00:44 Blank cells are okay but not an entirely blank row or
  • 00:48 blank column in the middle of our data.
  • 00:51 So this data is looking like it's in pretty good shape here.
  • 00:54 Let's go and start to filter down this list of transactions and
  • 00:57 see what we can actually learn about it.
  • 00:59 The way that we do that is, the easiest way is to grab our header row,
  • 01:04 go to the data tab and turn on the filter icon.
  • 01:09 You'll notice that when we do that,
  • 01:10 we get these little drop down arrows that appear here at the top of each column.
  • 01:14 Now, these things are really cool because when you click on them,
  • 01:17 it actually very quickly scans the entire list of data in that table.
  • 01:22 And it returns a list of the unique values within that column.
  • 01:26 So if you're just looking to figure out how many unique values there
  • 01:29 are in the column this is actually one way you can do it.
  • 01:32 Now if I want to select just the charges that have gone into say,
  • 01:36 beach bar, I could uncheck select all.
  • 01:39 Check beach bar, and you'll notice that it immediately filters all the transactions
  • 01:43 down to show me just the ones that are there.
  • 01:45 And we don't have to worry, nothing's disappeared.
  • 01:47 You can see that the rows have just been hidden, okay.
  • 01:49 That's what the little green bars are on the side here.
  • 01:52 So that's kinda cool.
  • 01:53 I can either dismiss this by clicking the X or just clicking back on worksheet grid.
  • 01:58 Now what is I want to look for something that might show up with some text in it.
  • 02:04 Specifically what I'm actually looking for right now is any card that actually
  • 02:08 has the letters IS in it because that would return visa and discover.
  • 02:12 So I can do this in a couple of ways.
  • 02:15 I can either go in and select contains and
  • 02:19 type in is, and you'll notice my list has filtered down now.
  • 02:26 Or I could clear the filter on this again, bring those transactions back.
  • 02:29 I could go into the search box right here, and type in IS, and it
  • 02:34 narrows down my list of transactions, or different list of items, in here as well.
  • 02:39 But the filters being applied immediately.
  • 02:41 So, if I'm happy with this, I could now go and click in here, and that window will go
  • 02:46 away, everything looks like it's okay here about working with dates.
  • 02:51 Actually the other thing I should probably point out before I move on here is that
  • 02:54 you'll notice these filters are successive.
  • 02:56 I first filtered the beach bar and
  • 02:58 then I also filtered down to my different card types, but
  • 03:02 it didn't bring me back any lobby bar chart just here at all, that's kinda nice.
  • 03:05 So this is drilling further and further into my data.
  • 03:07 What if I wanted to get into a specific date?
  • 03:10 Well, it's easy enough, you'll notice that the date filters,
  • 03:14 most of the time will show up probably collapsed in year.
  • 03:16 But you can drill into them nicely to get right into the individual date.
  • 03:20 So I could just select June 13th and
  • 03:22 filter my data right down to the transactions for that day.
  • 03:26 Or if I wanted to get a little more technical and
  • 03:28 advanced, I could click on choose one and there is a whole pile of options in here.
  • 03:33 So if I wanted to go for between certain dates, I could come back here and say,
  • 03:37 well let's just go and click on this little calendar.
  • 03:39 And I can set my charges between October 5th, 2015 and another date.
  • 03:46 Now obviously, with the date I have that's not gonna earn anything here.
  • 03:49 So I'm just going to go and
  • 03:51 click back over here and ignore that particular filter right now.
  • 03:57 Now, one of the really great things about this is that as I say, we can apply
  • 04:01 successive filters to just drill further and further into our data all the time.
  • 04:06 And that's awesome because if we have massive, massive lists with lots of
  • 04:09 columns and lots of rows, this could be really, really useful.
  • 04:12 But sometimes you'll also find that you can get a little bit lost and
  • 04:14 you need to clear your filters.
  • 04:16 So, there's a few ways to do that.
  • 04:18 To clear a filter on a specific column,
  • 04:20 you just click on one of the icons that has the filter icon showing.
  • 04:23 Notice that our arrows are different here.
  • 04:25 We've got an arrow with a filter,
  • 04:26 that shows us there's an active filter on this column.
  • 04:29 So we click on that, we could say clear filter.
  • 04:32 And it will bring back the charges for that specific column.
  • 04:34 See these two guys over here are still filtered.
  • 04:38 We could also go and click in a column and
  • 04:42 if we click the clear button, you'll notice that it clears all the filters.
  • 04:46 The date filter got cleared as well.
  • 04:48 And if I'm really lost and I don't know what's going on,
  • 04:51 maybe I've got 900 columns of data going across this thing.
  • 04:54 And I can't really tell what's going on, I could also come back and
  • 04:57 just grab this guy here.
  • 04:58 I'm gonna filter something for
  • 05:01 a second here just into a couple, just so we can see what it's doing in here.
  • 05:04 Then I'm gonna turn filters off all together by highlighting the row and
  • 05:09 clicking filter again.
  • 05:11 And that will unfilter all the things, so we never lose anything or
  • 05:15 leave anything hidden.
  • 05:16 But it allows us to clear those filters out.
  • 05:18 So that's how to work with filters in a nutshell to drill and
  • 05:20 out of your data as well.

Lesson notes are only available for subscribers.

Sorting
03m:49s
Contiguous Data
05m:06s
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