Filtering is a great feature of Excel that gives you a lot of control over which parts
of your worksheet are displayed.
You can use it to show only the information you need (or find something specific), while
hiding the rest of the data temporarily.
Now, it's important that you have a header row because the filtering tools actually get
added to the cells in the header row.
In this example, I have an equipment checkout log, and there are a lot of things to keep
track of, so I'd like to use some of the filters to simplify it.
First, go to the Data tab… and click the Filter command.
And you'll see these drop-down arrows added to your header row.
These are what you'll use to access all of the filtering options.
Here you'll see a list of all of the different values that appear in this column.
I'd like to just view the laptops and projectors, so I'll uncheck this box to quickly deselect
everything… then check Laptop… and Projector.
Click OK… and now the drop-down arrow has changed to a different icon to show that there
is a filter in this column.
We can narrow down this data even further by adding another filter to a different column.
Let's filter by the date that the items were checked out.
This shows all of the months that appear in this column.
So I'll uncheck the months that I don't want… then click OK.
To get rid of any filter, just click the icon again… and select Clear Filter.
So that's how you do simple filtering.
Now let's look at some more advanced techniques.
Using the same menu, you can also search for values that appear in this column.
In this example, a lot of our equipment is made by a company called Saris.
So if we enter that here…
Excel will look for a match anywhere in these cells, and then list only those results below.
To apply the filter, just click OK.
For a little more control, you can use text filters instead.
You can access these from the same menu… under Text Filters.
Here you can filter information that fits more specific criteria; for example, cells
that contain a certain number of characters, or data that doesn't contain a word that you
specify.
In this example, we're going to choose Does Not Contain… and then enter "laptop" to
exclude anything that contains this word (including laptops and laptop travel bags).
Click OK… and Excel will re-filter the data.
Next, let's say that I only want to show items where the ID number is between 3000 and 6000.
I can click the drop-down arrow… and go to Number Filters.
There are a lot of different options here, but for what I need, I'm going to choose Between.
Now I can type in whatever values I want in these two fields.
So this will show rows that are greater than or equal to 3000… and less than or equal
to 6000.
Click OK… and now all of these values are between 3000 and 6000.
Date filters are really cool because they can display data from a certain time period,
such as last year, next quarter, between two dates, and more.
In this example, I'd like to find out which items were checked out this week.
All you have to do is go to Date Filters… and you'll find many options to choose from.
I'll select This Week.
At this point, we have 3 different filters applied to the worksheet.
So it's filtering out most of the items and just showing the ones that meet all of the
criteria.
If you'd like to remove any of the filters, you can clear them one by one like I showed
you before… or you can turn off filtering all together—just click the Filter command
again.
As you can see, filtering can narrow things down really quickly.
And it gives you so many different options, you can probably find a use for it with any
worksheet.