Important notice for users of Office 2003 To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.
The Advanced Filter command on the Data menu lets you use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.) to filter a range, but it works differently from the AutoFilter command in several important ways.
 It displays the Advanced Filter dialog box instead of the Custom AutoFilter dialog box.
 You do not type the complex criteria in the Advanced Filter dialog box as you do in the Custom AutoFilter dialog box. Rather, you type the complex criteria in a criteria range on the worksheet and above the range you want to filter. Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the complex criteria.
 Although you can filter a range in place, like the AutoFilter command, the Advanced Filter command does not display dropdown lists for the columns.
 Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure there is at least one blank row between the criteria values and the range.
Example criteria range and data range for the criteria examples in step 2

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 



3 



4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
 In the rows below the column labels, type the criteria you want to match.
Criteria examples
Important
 Because the equal sign is used to indicate a formula when you type text or a value in a cell, Microsoft 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 
The following sections provide examples of complex criteria.
Multiple criteria in one column
Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")
To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range.
In the following data range (A6:C10), the criteria range (B1:B3) displays the rows that contain either "Davolio" or "Buchanan" in the Salesperson column (A8:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 

=Davolio 

3 

=Buchanan 

4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple criteria in multiple columns where all criteria must be true
Boolean logic: (Type = "Produce" AND Sales > 1000)
To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range.
In the following data range (A6:C10), the criteria range (A1:C2) displays all rows that contain "Produce" in the Type column and a value greater than $1,000 in the Sales column (A9:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 
=Produce 

>1000 
3 



4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple criteria in multiple columns where any criteria can be true
Boolean logic: (Type = "Produce" OR Salesperson = "Davolio")
To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in different rows of the criteria range.
In the following data range (A6:C10), the criteria range (A1:B3) displays all rows that contain "Produce" in the Type column or "Davolio" in the Salesperson column (A8:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 
=Produce 


3 

=Davolio 

4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple sets of criteria where each set includes criteria for multiple columns
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 rows.
In the following data range (A6:C10), the criteria range (B1:C3) displays the rows that contain both "Davolio" in the Salesperson column and a value greater than $3,000 in the Sales column, or displays the rows that contain "Buchanan" in the Salesperson and a value greater than $1,500 in the Sales column (A9:C10).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 

=Davolio 
>3000 
3 

=Buchanan 
>1500 
4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Multiple sets of criteria where each set includes criteria for one column
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.
In the following data range (A6:C10), the criteria range (C1:D3) displays rows that contain values between 5,000 and 8,000 and values less than 500 in the Sales column (A8:C10).

A 
B 
C 
D 
1 
Type 
Salesperson 
Sales 
Sales 
2 


>6000 
<6500 
3 


<500 

4 




5 




6 
Type 
Salesperson 
Sales 

7 
Beverages 
Suyama 
$5122 

8 
Meat 
Davolio 
$450 

9 
produce 
Buchanan 
$6328 

10 
Produce 
Davolio 
$6544 

Top of Page
Criteria to find text values that share some characters but not others
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.
How?
The following wildcard characters can be used as comparison criteria.
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?" 
In the following data range (A6:C10), the criteria range (A1:B3) displays rows with "Me" as the first characters in the Type column or rows with the second character equal to "u" in the Salesperson column (A7:C9).

A 
B 
C 
1 
Type 
Salesperson 
Sales 
2 
Me 


3 

=?u* 

4 



5 



6 
Type 
Salesperson 
Sales 
7 
Beverages 
Suyama 
$5122 
8 
Meat 
Davolio 
$450 
9 
produce 
Buchanan 
$6328 
10 
Produce 
Davolio 
$6544 
Top of Page
Criteria created as the result of a formula
You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) 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 range (in the examples below, 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 range is filtered.
The following subsections provide specific examples of criteria created as the result of a formula.
Top of Page
Filtering for values greater than the average of all values in the data range
In the following data range (A6:D10), the criteria range (D1:D2) displays rows that have a value in the Sales column greater than the average of all the Sales values (C7:C10). In the formula, "C7" refers to the filtered column (C) of the first row of the data range (7).

A 
B 
C 
D 
1 
Type 
Salesperson 
Sales 
Calculated Average 
2 



=C7>AVERAGE($C$7:$C$10) 
3 




4 




5 




6 
Type 
Salesperson 
Sales 

7 
Beverages 
Suyama 
$5122 

8 
Meat 
Davolio 
$450 

9 
produce 
Buchanan 
$6328 

10 
Produce 
Davolio 
$6544 

Top of Page
Filtering for text by using a casesensitive search
In the data range (A6:D10), the criteria range (D1:D2) displays rows that contain "Produce" in the Type column by using the EXACT function to perform a casesensitive search (A10:C10). In the formula, "A7" refers to the filtered column (A) of the first row of the data range (7).

A 
B 
C 
D 
1 
Type 
Salesperson 
Sales 
Exact Match 
2 



=EXACT(A7, "Produce") 
3 




4 




5 




6 
Type 
Salesperson 
Sales 

7 
Beverages 
Suyama 
$5122 

8 
Meat 
Davolio 
$450 

9 
produce 
Buchanan 
$6328 

10 
Produce 
Davolio 
$6544 

 Click a cell in the range.
 On the Data menu, point to Filter, and then click Advanced Filter.
 To filter the range by hiding rows that don't match your criteria, click Filter the list, inplace.
To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upperleft corner of the area where you want to paste the rows.
 In the Criteria range box, enter the reference for the criteria range, including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
 To change how the data is filtered, change the values in the criteria range and filter the data again.
Notes
 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 range of data 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.
 When you copy filtered rows to another location, you can specify which columns to include in the copy. Before filtering, copy the column labels for the columns 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.