Filter by using advanced criteria

If the data you want to filter requires criteria across multiple fields, such as filtering by multiple conditions that must all be true, or showing rows that match any of several different conditions (such as Type = "Produce" OR Salesperson = "Davolio"), you can use the Advanced Filter dialog box.

To open the Advanced Filter dialog box, click Data > Advanced.

Screenshot of Sort and Filter Section of Data tab

Advanced Filter	Example
Overview of advanced filter criteria
Multiple criteria, one column, any criteria true	Salesperson = "Davolio" OR Salesperson = "Buchanan"
Multiple criteria, multiple columns, all criteria true	Type = "Produce" AND Sales > 1000
Multiple criteria, multiple columns, any criteria true	Type = "Produce" OR Salesperson = "Buchanan"
Multiple sets of criteria, one column in all sets	(Sales > 6000 AND Sales < 6500 ) OR (Sales < 500)
Multiple sets of criteria, multiple columns in each set	(Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500)
Wildcard criteria	Salesperson = a name with 'u' as the second letter

Overview of advanced filter criteria

Advanced Filter works differently from Filter in several important ways.

It displays the Advanced Filter dialog box instead of the AutoFilter menu.
You create a criteria range (separate cells above your data) where you enter your filter conditions, then tell the Advanced Filter dialog box to use that range.
Advanced Filter does NOT auto-update when you change criteria values

Note: Advanced Filter remains available for complex filtering scenarios, though newer features like Copilot in Excel can now assist users with data analysis and filtering through natural language queries as an alternative approach for some use cases.

Understanding AND vs OR Logic

Logic Type	How to Set Up	Example	What it finds
AND logic (all criteria must be true)	Put criteria in same row	Type = "Produce" in column 1 Sales > 1000 in column 2 (both in same row)	Only rows where Type IS "Produce" AND Sales IS greater than 1000
OR logic (any criteria can be true)	Put criteria in different row	Row 1: Type = "Produce" Row 2: Type = "Meat" (different rows, same column)	Rows where Type IS "Produce" OR Type IS "Meat" (or both)

Sample data

The following sample data is used for all procedures in this article.

The data includes three blank rows above the list range that will be used as a criteria range (A1:C4) and a list range (A6:C10). The criteria range has column labels and includes at least one blank row between the criteria values and the list range.

To work with this data, select it in the following table, copy it, and then paste it in cell A1 of a new Excel worksheet.

Type	Salesperson	Sales



Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

In this example, the resulting worksheet will look like this, where the filter criteria range is outlined in blue and the list range (the data you want to filter) is outlined in red.

Comparison operators

You can compare two values by using the following operators. When two values are compared by using these operators, the result is a logical value—either TRUE or FALSE.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	A1=B1
> (greater than sign)	Greater than	A1>B1
< (less than sign)	Less than	A1<B1
>= (greater than or equal to sign)	Greater than or equal to	A1>=B1
<= (less than or equal to sign)	Less than or equal to	A1<=B1
<> (not equal to sign)	Not equal to	A1<>B1

Using the equal sign to type text or a value

Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

=''= entry ''

Where entry is the text or value you want to find. For example:

What you type in the cell	What Excel evaluates and displays
="=Davolio"	=Davolio
="=3000"	=3000

Considering case-sensitivity

When filtering text data, Excel doesn't distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see the section Wildcard criteria.

Using pre-defined names

You can name a range Criteria, and the reference for the range will appear automatically in the Criteria range box. You can also define the name Database for the list range to be filtered and define the name Extract for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes, respectively.

Creating criteria by using a formula

You can use a calculated value that is the result of a formula as your criterion. Remember the following important points:

The formula must evaluate to TRUE or FALSE.
Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:

=''= entry ''
Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the list range (in the examples that follow, Calculated Average and Exact Match).

If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the list range is filtered.
The formula that you use for criteria must use a relative reference to refer to the corresponding cell in the first row of data.
All other references in the formula must be absolute references.

Multiple criteria, one column, any criteria true

Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")

Use this when you want to filter for rows where a single column matches ANY of several values. Both rows with Davolio AND rows with Buchanan will be shown.

To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range. For the example, enter the following into the first two rows of the criteria range:

Type	Salesperson	Sales
	="=Davolio"
	="=Buchanan"

Click a cell in the list range.
On the Data tab, in the Sort & Filter group, click Advanced.
Choose to either Filter the list, in-place, hide rows that don't match your criteria, or Copy to another location, copy rows that match your criteria to another area of the worksheet.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$3.

Using the example, the filtered result for the list range is:

Type	Salesperson	Sales
Meat	Davolio	$450
produce	Buchanan	$6,328
Produce	Davolio	$6,544

Multiple criteria, multiple columns, all criteria true

Boolean logic: (Type = "Produce" AND Sales > 1000)

To find rows that meet multiple criteria in multiple columns, type all the criteria in the same row of the criteria range. For the example, enter:

Type	Salesperson	Sales
="=Produce"		>1000

Click a cell in the list range.
On the Data tab, in the Sort & Filter group, click Advanced.
Choose to either Filter the list, in-place, hide rows that don't match your criteria, or Copy to another location, copy rows that match your criteria to another area of the worksheet.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$2.

Using the example, the filtered result for the list range is:

Type	Salesperson	Sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

Multiple criteria, multiple columns, any criteria true

Boolean logic: (Type = "Produce" OR Salesperson = "Buchanan")

To find rows that meet multiple criteria in multiple columns where any criteria can be true, type the criteria in the different columns and rows of the criteria range. For the example, enter:

Type	Salesperson	Sales
="=Produce"
	="=Buchanan"

Click a cell in the list range.
On the Data tab, in the Sort & Filter group, click Advanced.
Choose to either Filter the list, in-place, hide rows that don't match your criteria, or Copy to another location, copy rows that match your criteria to another area of the worksheet.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$B$3.

Using the example, the filtered result for the list range is:

Type	Salesperson	Sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

Multiple sets of criteria, one column in all sets

Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )

To find rows that meet multiple sets of criteria where each set includes criteria for one column, include multiple columns with the same column heading. For the example, enter:

Type	Salesperson	Sales	Sales
		>6000	<6500
		<500

Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Choose to either Filter the list, in-place, hide rows that don't match your criteria, or Copy to another location, copy rows that match your criteria to another area of the worksheet.
- Tip: When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$D$3.

Using the example, the filtered result for the list range is:

Type	Salesperson	Sales
Meat	Davolio	$450
produce	Buchanan	$6,328

Multiple sets of criteria, multiple columns in each set

Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )

To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate columns and rows. For the example, enter:

Type	Salesperson	Sales
	="=Davolio"	>3000
	="=Buchanan"	>1500

Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Choose to either Filter the list, in-place, hide rows that don't match your criteria, or Copy to another location, copy rows that match your criteria to another area of the worksheet.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$3.

Using the example, the filtered result for the list range would be:

Type	Salesperson	Sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

Wildcard criteria

Boolean logic: Salesperson = a name with 'u' as the second letter

To find text values that share some characters but not others, do one or more of the following:

Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."

Use a wildcard character.

Use	To find
? (question mark)	Any single character For example, sm?th finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"

Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.

In the rows below the column labels, type the criteria that you want to match. Using the example, enter:

Type	Salesperson	Sales
="=Me*"
	="=?u*"

Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.
Choose to either Filter the list, in-place, hide rows that don't match your criteria, or Copy to another location, copy rows that match your criteria to another area of the worksheet.
In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$B$3.

Using the example, the filtered result for the list range is:

Type	Salesperson	Sales
Beverages	Suyama	$5,122
Meat	Davolio	$450
produce	Buchanan	$6,328

How to Remove or Clear an Advanced Filter

After applying an Advanced Filter, you may want to remove it to see all your data again. Here's how:

Click on any cell in your filtered data range.
Go to the Data tab.
In the Sort & Filter group, click Clear.
All rows will be displayed again.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Filter by using advanced criteria

Overview of advanced filter criteria

Understanding AND vs OR Logic

Sample data

Comparison operators

Using the equal sign to type text or a value

Considering case-sensitivity

Using pre-defined names

Creating criteria by using a formula

Multiple criteria, one column, any criteria true

Multiple criteria, multiple columns, all criteria true

Multiple criteria, multiple columns, any criteria true

Multiple sets of criteria, one column in all sets

Multiple sets of criteria, multiple columns in each set

Wildcard criteria

How to Remove or Clear an Advanced Filter

Need more help?

Need more help?

Want more options?

Was this information helpful?

Thank you for your feedback!