Filters in Excel are used to reduce the amount of data displayed inside a worksheet. Depending on whether you filter text, numbers, or dates you will get different filtering options. For example, you could filter people by age to check whether they are employees or underage, or you could filter cities with the word “New”, like “New York”, or “New Delhi”.
In this lesson, I’ll show you how to use filters for different types of data, so you can narrow down the result to the information you need.
There are two ways to apply a filter in Excel. First click any cell inside a table, where you want filtering applied. Next, choose one of these two options:
- Navigate to HOME >> Editing >> Sort & Filter >> Filter.
- Use the keyboard shortcut – Ctrl + Shift + K
After you do that, the filtering icons will show up. You can click them to expand filtering options.
The first type of data to filter is text. If you click a filter icon inside a column with text data, text filtering options will appear. Let’s click the filter icon next to City.
We will filter only these positions, where there is the word New inside a cell.
As you can see it returned two positions: New Orleans, and New York. You can unselect the values you don’t want.
After you apply a filter to a column, the icon changes its look.
If you click any position inside the Text Filters, it’s going to open the Custom Autofilter window, which is the same for all types of data.
Because you clicked Contains, the contains option appears in the first dropdown. You can select the second option, and click the Or radio button.
In this case, the filtered values are those that contain the word new, los, or both.
And this is the result we get.
The Contains option works in this case. But if there was a city with the substring new or los inside, it would also be filtered.
In this case, you should choose a different option – begins with.
The other option you have are Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain.
Now, click the filter icon next to the Age column. The Text Filters changed to Number Filters with the new set of options.
Here, you can choose Between and select only people with a certain age group, or choose Top 10 to select 10 oldest people on the list.
In the Text Filters, no matter which option you chose, the Custom Autofilter window would appear. In Number Filters there are three options:
Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than Or Equal To, Between, Custom Filter will display Custom Autofilter.
To set the number of top or bottom values use Top 10 Autofilter.
Above and Below Average: They won’t display any window, just filter values right away.
With filtering dates, you have a huge number of Filtering options.
If you expand the All Dates in the Period option, you are going to get additional filters.
If you fill cells with color or change the font color, additional options are going to appear. Take a look at how it works in our example.
Inside the table, the most efficient employees are highlighted with green color, and the least efficient with red, the rest is not highlighted with any color.
There are three fills: green, red, and no fill, and two font colors: black and white.
Now, click a filter button inside a header and navigate to Filter by Color.
Inside Filter by Cell Color, there are three different types of fill: green, red, and No Fill – the same as fills inside the table.
In Filter by Font Color, there are two types: white, and Automatic – which is black in most cases.
Using wildcard characters
The asterisk (*) character represents a variable number of characters (from zero to infinity), and the question mark (?), which represents a single character.
Here’s how it works. Let’s say that you want to find a name that begins with the letter a.
But the same effect you would get if you use the option Begins With. That’s true, so let’s check something a bit more complicated.
Let’s display all names that begin with the letter a, and end with the letter a. Let’s modify our example and see what you can get.
The same effect you can achieve with the following Custom AutoFilter.
And this is the result.
Let’s see how the question mark works as a wildcard character.
In the next example, we are going to display names that start with a, and end with a, but this time add another restriction – names should have 6 characters.
Here’s what it looks like.
Now, the filter displays only rows with these two names: Albina and Alisha.
You can also use the wildcard characters inside Custom AutoFilter. They are easy to use and make our work much easier.
After you applied a filter, it’s time to clear them. You can clear a single filter, or you can clear it all at once.
To clear a single filter, click the filter icon, and navigate to Clear Filter From. In our case, it will be the First Name.
If you have more than one filter applied, you can remove them one by one, or clear them all at once.
Here’s how to do it.
Navigate to Home >> Editing >> Sort & Filter >> Clear.