Using advanced filtering

The criteria range holds the information that Excel uses to filter the list. It must conform to the following specifications:

• It consists of at least two rows, and the first row must contain some or all field names from the list. An exception to this is when you use computed criteria. Computed criteria can use an empty header row.
• The other rows consist of your filtering criteria.

Although you can put the criteria range anywhere in the worksheet, you should avoid putting the criteria range in rows that are used by the list. Because some of these rows are hidden when the list is filtered, you may find that your criteria range is no longer visible after the filtering takes place. Therefore, you should generally place the criteria range above or below the list.

For example, a worksheet contains real estate listings. The criteria range, located in A1:E2, is positioned above the list that it uses. Notice that not all field names appear in the criteria range. Fields that aren't used in the selection criteria need not appear in the criteria range.

In this example, the criteria range has only one row of criteria. The fields in each row of the criteria range (except for the header row) are joined with an AND operator. Therefore, the filtered list shows rows in which:

• the Status column equals in Process AND
• the Component field is FI AND
• the Process-Status field is Customer Test.

In other words, the filtering will display only in-process tickets from the FI component with process-status Customer Test.

To perform the filtering, on the Data tab, in the Sort & Filter group, click Advanced:

Excel displays the Advanced Filter dialog box:

Specify the List Range and the Criteria Range, and make sure that the option labeled Filter the List, In-Place is selected. Click OK, and the list is filtered by the criteria that you specified.

Multiple criteria

If you use more than one row in the criteria range, the rows of criteria are joined with an OR operator:

This is an example of filtering that could not be done with auto filtering. A criteria range can have any number of rows, each of which is joined to the others with an OR operator:

See also this tip in French: Comment utiliser le filtrage avancé.