Show All

Hide All

Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) you specify for a column. Microsoft Excel provides two commands for filtering ranges: AutoFilter and Advanced Filter.

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.

What do you want to do?

Use AutoFilter for simple criteria and to filter by selection

Use Advanced Filter for more complex criteria

Use AutoFilter for simple criteria and to filter by selection

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).

Top of Page

Use Advanced Filter for more complex criteria

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 drop-down lists for the columns.

For more information, see the help topic, Examples of complex criteria.

Top of Page