Unlike sorting, filtering does not rearrange a range. Filtering temporarily hides rows you do not want displayed.
When Excel filters rows, you can edit, format, chart, and print your range subset without rearranging or moving it.
When you use the AutoFilter command, AutoFilter arrows 
appear to the right of the column labels in the filtered range.
Unfiltered range
Filtered range
Microsoft Excel indicates the filtered items with blue.
You use custom AutoFilter to display rows that contain either one value or another. You can also use custom AutoFilter to display rows that meet more than one condition for a column; for example, you might display rows that contain values within a specific range (such as a value of Davolio).
The Advanced Filter command can filter a range in place like the AutoFilter command, but it does not display drop-down lists for the columns. Instead, you type the criteria you want to filter by in a separate criteria range above the range. A criteria range allows for more complex criteria to be filtered.
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.