| Type | Salesperson | Sales |
|---|---|---|
| Type | Salesperson | Sales |
|---|---|---|
| Beverages | Suyama | 5122 |
| Meat | Davolio | 450 |
| Produce | Buchanan | 6328 |
| Produce | Davolio | 6544 |
Multiple conditions in a single column
If you have two or more conditions for a single column, type the criteria directly below each other in separate rows. For example, the following criteria range displays the rows that contain either "Davolio," "Buchanan," or "Suyama" in the Salesperson column.
| Salesperson |
|---|
| Davolio |
| Buchanan |
| Suyama |
One condition in two or more columns
To find data that meets one condition in two or more columns, enter all the criteria in the same row of the criteria range. For example, the following criteria range displays all rows that contain "Produce" in the Type column, "Davolio" in the Salesperson column, and sales values greater than $1,000.
| Type | Salesperson | Sales |
|---|---|---|
| Produce | Davolio | >1000 |
One condition in one column or another
To find data that meets either a condition in one column or a condition in another column, enter the criteria in different rows of the criteria range. For example, the following criteria range displays all rows that contain either "Produce" in the Type column, "Davolio" in the Salesperson column, or sales values greater than $1,000.
| Type | Salesperson | Sales |
|---|---|---|
| Produce | ||
| Davolio | ||
| >1000 |
One of two sets of conditions for two columns
To find rows that meet one of two sets of conditions, where each set includes conditions for more than one column, type the criteria in separate rows. For example, the following criteria range displays the rows that contain both "Davolio" in the Salesperson column and sales values greater than $3,000, and also displays the rows for salesperson Buchanan with sales values greater than $1,500.
| Salesperson | Sales |
|---|---|
| Davolio | >3000 |
| Buchanan | >1500 |
More than two sets of conditions for one column
To find rows that meet greater than two sets of conditions, include multiple columns with the same column heading. For example, the following criteria range displays sales that are between 5,000 and 8,000 in addition to sales that are less than 500.
| Sales | Sales |
|---|---|
| >5000 | <8000 |
| <500 |
Conditions created as the result of a formula
You can use a calculated value that is the result of a formula as your criterion. When you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the range. For example, the following criteria range displays rows that have a value in column C greater than the average of cells C7:C10.
| =C7>AVERAGE($C$7:$C$10) |
Notes
- The formula you use for a condition must use a relative reference to refer to the column label (for example, Sales) or the corresponding field in the first record. All other references in the formula must be absolute references, and the formula must evaluate to TRUE or FALSE. In the formula example, "C7" refers to the field (column C) for the first record (row 7) of the range.
- You can use a column label in the formula instead of a relative cell reference or a range name. When Microsoft 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.
- When evaluating data, Microsoft Excel does not distinguish between uppercase and lowercase characters.
All items that begin with that text are filtered. For example, if you type the text Dav as a criterion, Microsoft Excel finds "Davolio," "David," and "Davis." To match only the specified text, type the following formula, where text is the text you want to find. =''=text''
To find text values that share some characters but not others, use a wildcard character.
The following wildcard characters can be used as comparison criteria for filters, and when searching and replacing content.
| 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?" |
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 upper-left corner of the area where you want to paste the rows.
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.
- 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.