How To Filter In Excel:
Advanced Filters And Autofiler Explained

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Massive spreadsheets are difficult to deal with. Searching for the things you want can be difficult. Scrolling through thousands of rows is even worse.

Excel’s filtering abilities can help.

By using AutoFilter and advanced filters, you can whittle the visible data down to just the information you want to see. And that makes your job easier.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

There’s a lot of filter functionality to go over, and it’ll be much easier to learn if you can follow along.

Grab our free example workbook below and work through it with us!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Using Excel’s AutoFilter

Excel makes it incredibly easy to filter your data. Open up the example workbook and you’ll see what I mean.

On the first page, you’ll see that we have a list of car makes, models, model years, and values. We can use the AutoFilter to get all sorts of information from these columns.

To enable the AutoFilter, click on any cell in the table, and click the Filter button in the Data tab of the Ribbon. You’ll see some arrows appear in your header row:

filter-button

If that ever doesn’t work, highlight the cells in the range you’d like to filter, then click the Filter button.

Kasper Langmann, Co-founder of Spreadsheeto

Those little arrows might not seem like much, but they open up a lot of power.

Click on the filter arrow in the Make column, and you’ll see lots of options:

filter-options

At the top, you’ll see buttons for sorting your data alphabetically (or in reverse) by the selected column.

There’s also Sort by Color, which we covered in our article on sorting, and Text Filters, which we’ll get to in a moment.

For now, though, look at the list at the bottom of the pop-up menu. You’ll see a list of the different car makes in the list, each with a checkbox next to its name.

Unchecking one of those boxes filters out that particular make from the list. Try unchecking “Honda,” and you’ll see that the first entry in the list disappears:

filter-checkbox

Go back in, click on that same box to add a checkmark, and you’ll see that the first entry comes back.

Now, try removing the check from the “(Select All)” box.

filter-select-all

This removes the check from every entry in the list. Excel won’t let you click OK if you have a completely empty list, though.

So check the box for BMW and click OK.

filtered-list

You can see in the image above that the filter arrow changed to an image of a filter. This reminds you which columns have applied filters at any given time.

Kasper Langmann, Co-founder of Spreadsheeto

Click Clear Filter From “Make” in the menu, and you’ll have every one of the original entries back.

clear-filter

Take note of the search box directly above the list, as well.

If you start typing the value you’re looking for, Excel will show you the matching values:

filter-search

Filtering by color

If you use colors to code your data (perhaps with the always-useful conditional formatting), filtering by color is a great feature to know about.

And it’s as easy as you’d expect.

I’ve filled a few cells with random colors to show you how it works.

colored-cells

I used three colors here, but filtering works with any number of different colors.

Kasper Langmann, Co-founder of Spreadsheeto

To view only a specific color in your list, click the filter arrow, hover over Filter by Color, and select a color:

filter-color

When you click on a color, you’ll see only the cells with a matching fill color:

filtered-color

You can also filter for cells that don’t have a background color. Just select No Fill from the Filter by Color options.

Kasper Langmann, Co-founder of Spreadsheeto

To view all your cells again, click Clear Filter From “Make” (or the corresponding name of your own column), and you’ll have the entire list again.

Pro tip: filtering by multiple colors

You might notice that you don’t have the option of selecting multiple colors. This is one place where filtering by color falls short of standard filtering. But you can do a bit of a workaround to get a similar effect.

To get started, open the filter menu and go to Sort by Color > Custom Sort. Use the options here to sort the cell colors in the order you want them (if you need a refresher, check out our article on sorting).

When you have them set up how you’d like, click OK.

sorted-colors

Once you’ve done that, select the rows that you don’t want to see, right-click, and select Hide.

hide-cells

It’s not an especially elegant solution, but it works.

Filtering for specific text

AutoFilter also gives you the ability to filter for specific strings of text. If you want to find all entries that start with a B, for example, you can. Or every entry that doesn’t contain the string “gh67.”

To find these options, click the filter arrow, click Text Filters, and select an option. We’ll select Begins With…

text-filter-begins-with

Once you’ve made a selection, you’ll see the Custom AutoFilter box:

custom-autofilter

From here, select the type of filter you want, enter the relevant search information, and click OK.

Kasper Langmann, Co-founder of Spreadsheeto

For example, if we want to find the values in the Make column that start with B or C, we’d run the AutoFilter like this:

custom-autofilter-example

After we hit OK, we get a list of all the cars whose makes start with B or C:

custom-autofilter-results

The other custom text AutoFilter options work the same way. Select the type of filter you want, enter the relevant letters or words, and hit OK.

And remember that if you want to access more filtering options than those available in the Text Filters menu, click Custom Filter… at the bottom.

filtered-color

You can also filter for cells that don’t have a background color. Just select No Fill from the Filter by Color options.

Kasper Langmann, Co-founder of Spreadsheeto

To view all your cells again, click Clear Filter From “Make” (or the corresponding name of your own column), and you’ll have the entire list again.

Using number filters

Like the text filters, you can access number filters that let you filter for cells that meet certain criteria.

Click on the arrow in the Value column, and select Number Filters.

number-filters

As you can see, there are plenty of options, most of which are self-explanatory. The most interesting options are Top 10Above Average, and Below Average.

And, as with the text filters, you can use the Custom Filter… option to create your own filter according to your specifications.

Try creating a few number filters to find cars that are worth more than $20,000 and cars that are worth between $15,000 and $17,000.

Kasper Langmann, Co-founder of Spreadsheeto

Advanced filters

With these powerful tools, you might be wondering what more you could possibly get from “advanced” filters. And there are actually quite a few things.

Click on the Advanced button in the Sort & Filter section to see what I mean.

advanced-filter-button

You’ll see a new window with some unfamiliar-sounding fields.

advanced-filter-button

As you might expect, the List range is the data that you want filtered.

Criteria range, however, is something we haven’t dealt with yet. Instead of simply clicking on the items you’d like to see in your filtered list, advanced filters require that you create a criteria range.

Here’s how we’ll set that up. In cells F1, G1, and H1, type “Model,” “Year,” and “Value.”

criteria-range

We can now use these columns to create our filter criteria.

For example, if we type “Fit” in the Model column, we will filter our list so that we only see cars with the model name “Fit.”

To engage the filter, click on Advanced again, click into the Criteria range box, and select F1:H2.

criteria

Note that you can type in the criteria range or click-and-drag to select it.

Once you’re done, hit OK to filter.

Try it for yourself! Type in a model name, then open the advanced filter dialog and select your criteria range. When you hit OK, you’ll only see a subset that matches your criteria.

Kasper Langmann, Co-founder of Spreadsheeto

You can also use logical statements to filter.

For example, you can put “>30000” in the Value column to filter for cars that are worth more than $30,000.

And you can use wildcards for text, too. “M*” would return models that start with M, for example.

Pro tip: combining criteria

Now that you understand the basics of using a criteria range, we can talk about some of the more powerful things you can do.

Including two different criteria on the same row, for example, is equivalent to filtering for an AND logical statement:

criteria-range-and

As you can see in the screenshot above, we’ve filtered for any car with a model name that starts with M* AND is worth over $30,000.

If you put criteria in different rows, it functions as an OR statement:

criteria-range-or

In the image above, we’re filtering for any cars that have the model name “Fit,” OR that were made in 2000, OR that are worth less than $50,000.

Combining the AND and OR functionalities is where advanced filters get really cool.

Here’s an example:

criteria-range-and-or

This filter will return any car that was made in 2000 AND is worth more than $40,000, OR has a name that starts with M AND is worth more than $40,000.

You can combine many different AND and OR statements in this manner.

You probably noticed the Action options at the top of the advanced filter dialog. There are two options: Filter the list, in-place; and Copy to another location.

We’ve been using the filter-in-place option, but if you want to copy the results of your filter to a new location, select the radio button next to the latter option.

You’ll also need to tell Excel where to put the copied list. That’s what the final field, Copy to, is for. Type or click to tell Excel where it should put the newly copied list.

copy-to

Excel only lets you copy a filtered list to the current sheet. If you want it on another sheet, run the filter, cut the filtered list, and paste it on the sheet where you’d like it.

Kasper Langmann, Co-founder of Spreadsheeto

Finally, there’s the Unique records only checkbox at the bottom. Check this if you want to remove duplicates from your copied list.

And don’t forget: to clear your advanced filter, click the Clear button in the Sort & Filter section of the Ribbon.

clear-advanced-filter

Filter like an Excel master

We know that’s a lot of information to take in. But that’s because Excel’s filtering capabilities are second-to-none.

It does a lot of the work for you, but if you’re willing to put in the time to learn how to properly use filters, you’ll be able to slice and dice your data in any way necessary.

Especially if you get good at using advanced filters. With all the capabilities of AutoFilter, it might seem unnecessary, but there may come a day when it will save you hours of extra work. So don’t forget about it.

As with anything in Excel, filtering takes a while to get the hang of. But stick with it, and you’ll seriously step up your Excel game!

Kasper Langmann, Co-founder of Spreadsheeto
2019-10-14T11:43:13+00:00