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.
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
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.
- Add a new row at the top of the source data. This row will include unique headers (if you don't have this already).
- There should not be any blank rows after the header row or within the data.
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.
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
| Operator | Descriptions | Criteria 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.
|
Here some of examples of how you can use Advanced Filters for filtering text strings.
Operators
| Criteria | Description |
|
'=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. |
| Fire | Filter cells which begin with "Fire". |
| <>Fire | Filter cells which are not exactly equal to "Fire". Any cell contains "Fire" as part of their contents will remain. |
| >Fire | Filter cells which are alphabetically ordered after "Fire". |
| <Fire | Filter cells which are alphabetically ordered before "Fire". |
Wildcards
| Operator | Descriptions | Criteria 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.
- Select the source data and open the Advanced Filters dialog like before.
- Make sure to select Copy to another location. This will enable the Copy to input.
- Select the headers.
- Click OK.
All filtered results will be listed under the selected header.
