Using advanced filtering

In addition to manual data filtering Excel enables fully automated filtering based on data from the specified range of cells. Before you can use the advanced filtering feature, you must set up a criteria range. A criteria range is a designated range on a worksheet that conforms to certain requirements.

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.

In 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 what aren't used in the selection criteria need not appear in the criteria range.

Criteria range in Excel 2016

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 "FI" component with process-status "Customer Test".

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

Sort and Filter in Excel 2016

Excel displays the Advanced Filter dialog box:

Advanced Filter in Excel 2016

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

Criteria Result in Excel 2016

Multiple criteria

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

OR criteria range in Excel 2016

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

OR criteria result in Excel 2016