The arrow buttons, FILTER function, and Power Query are popular methods for filtering data in Excel. Unless you are using Power Query, these methods may not be enough when working with two or more criteria. The more powerful Advanced Filter feature can help for more complex scenarios. In this guide, we’re going to show you how to use Advanced Filters in Excel.

Download Workbook

Advanced Filters

Advanced filters work a bit differently than regular filters. Instead of using a dropdown menu with a list of available items, you need to prepare a range that contains the criteria.

On the other hand, you have options to generate the results from another range without altering the original data or list unique records.

How to use Advanced Filters in Excel 01

How to use Advanced Filters

You can find the Advanced Filters button under the Advanced option in the Data tab.

Source Data

The source data should be in a table form.

  1. Add a new row at the top of the source data. This row will include unique headers (if you don't have this already).
  2. There should not be any blank rows after the header row or within the data.

Tip: You can convert your data into an Excel Table to ensure the conditions above are met while also having some advanced features available: How to create an Excel Table.

Criteria

Criteria for Advanced Filters should be a separate table that contains the headers of the columns you want to filter and the conditions you want to add under their corresponding columns. For example, the following example filters rows where Type containing “Fire” and Total is less than 560.

How to apply Advanced Filter

Once the source data and criteria range are ready, click on the Advanced button under the Data tab of the Ribbon.

Advanced Filter dialog will pop up. Set the source data and criteria references from the List range and Criteria range inputs.

How to use Advanced Filters in Excel 05

Tip: Selecting a cell in your source data will help Excel recognize and set its reference as List Range in the Advanced Filter dialog.

Clicking OK on in the example above will apply the filter to the source data by hiding the rows, just like in regular filtering. You will see duplicate records as well, unless you enable the Unique records only option.

The biggest disadvantage of the Advanced Filter feature is that it requires re-opening this dialog every time you want to use it. It is not dynamic unless you create a macro for this.

Criteria options

Advanced Filters allows you to cover different scenarios with help from operators and wildcards. Although establishing numeric conditions is straightforward, text-based criteria can be a bit more tricky. Let’s see some samples.

Numbers

OperatorDescriptionsCriteria Example
=Equal to=500
>Greater than>500
<Less than<500
>=Greater than or equal to>=500
<=Less than or equal to<=500
<>Not equal to<>500

 

Since date values are numbers for Excel, you can use operators with dates as well.

Text strings

You can use wildcard characters for text strings along with operators.

Asterisk (*)Any value of zero or more
Question mark (?)Any single character
Tilde (~)

Escape for an actual question mark, asterisk, or tilde character.

  • A literal asterisk  (~*)
  • A literal question mark (~?)
  • A literal tilde (~~)

These wildcards can used in other functions like SUMIFS, VLOOKUP or SEARCH: How to use Wildcard criteria in Excel formulas

Here some of examples of how you can use Advanced Filters for filtering text strings.

Operators

CriteriaDescription

'=Fire

or

="=Fire"

Filter cells which are exactly equal to "Fire". Since the equal sign “=” starts a formula, you should either use a single quote (') or formula which will return the equal sign.
FireFilter cells which begin with "Fire".
<>FireFilter cells which are not exactly equal to "Fire". Any cell contains "Fire" as part of their contents will remain.
>FireFilter cells which are alphabetically ordered after "Fire".
Filter cells which are alphabetically ordered before "Fire".

 

Wildcards

OperatorDescriptionsCriteria Example
*Fire*Filter cells which contain "Fire".

FIRE, FLYING

FIRE

="=*Fire"Filter cells which end with "Fire".Poison, Fire
="=G????"Filter cells which have 5-character strings starts with "G".Grass
~*Filter cells which start by * character.*Chain breeding

AND / OR logic in Advanced Filters

The source data is filtered by cells that meet both conditions. This is the AND logic. All criteria should be met by a row for it to be visible. Here, all cells under the Type column start with "Fire" AND all values under the Total column are less than 560.

You can use Advanced Filters with OR logic by entering the criteria into different columns. The following is an example for filtering if all cells under the Type column that contain "Fire" OR all values under Total column are less than 560.

Note that "Arcanine" and "Entei" are listed, even though 555 and 580 are greater than 560 due to "Fire" under Type column.

Copy to another location

Our final tip for using Advanced Filters is the Copy to another location feature. You can populate the filtered results in a specific range, and you do not have to populate all columns. The results are populated by the column headers you have determined before.

First, you need to write the column names into the cells where you want to populate the filtered data. Make sure there is enough space below for the results.

  1. Select the source data and open the Advanced Filters dialog like before.
  2. Make sure to select Copy to another location. This will enable the Copy to input.
  3. Select the headers.
  4. Click OK.

All filtered results will be listed under the selected header.