About filtering

Try Office 2010 In Excel 2010, you can take advantage of improved calculation options.
Read a blog post or try Office 2010!

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

When you use the AutoFilter command, AutoFilter arrows Field arrow appear to the right of the column labels in the filtered range.

List filtered for Davolio

Callout 1 Unfiltered range
Callout 2 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 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 Top of Page

 
 
Applies to:
Excel 2003