Using advanced filtering

Excel 2016
In addition to manual data filtering, Excel enables fully automated filtering based on data from the specified range of cells. Before using the advanced filtering feature, you must set up a criteria range. A criteria range is a designated range on a worksheet that conforms to specific 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 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. 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.

Criteria range in Excel 365

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:

Advanced button of Sort and Filter in Excel 365

Excel displays the Advanced Filter dialog box:

Advanced Filter in Excel 365

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 specified criteria:

Criteria Result in Excel 365

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 365

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:

OR criteria result in Excel 365

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.