Excel custom autofilter more than 2 criteria

Quick links

How to filter in Excel

If you have an Excel sheet on which you would like to filter on several criteria, you have three different ways, depending if you want to filter on one, two, or multiple criteria, in which case you need to use the advanced filter Excel.

It is possible to use multiple filters in Excel, with Autofilter, in order to have advancedfilter applied to an open spreadsheet, and find the Excel match multiple criteria on one or more columns.

Excel custom autofilter more than 2 criteria : Raw data set to filter on multiple criteria
Raw data set to filter on multiple criteria

1 - Excel 2013 filter on one criteria

The first and easiest way, to filter on one or maximum 2 criteria, is to apply a filter in menu Data => Filter.

Excel custom autofilter more than 2 criteria : Apply standard filter
Apply standard filter

With filter applied, on the column on which you want to filter, click on the arrow, and, in Search field, enter the string you are looking for:

- ABC for entries containing ABC,

- ABC* for entries starting with ABC,

- *ABC for entries ending with ABC.

This is also the way to do an Excel filter multiple columns, by selecting the filter to apply in each column regarding the data stored in that column.

It is even possible to do an Excel filter multiple criteria, as one criteria can be selected by column, and it can be different for each of them.

Excel custom autofilter more than 2 criteria : Apply single filter, or open menu to apply 2 filters
Apply single filter, or open menu to apply 2 filters

2 - Double filter in Excel

With filter applied, on the column on which you want to filter, click on the arrow, and, in Text Filters menu, select Contains option.

In the open box, you can then enter one or two criteria - if you select OR, both will be applied. If you select AND (by default), only entries meeting the two criteria will be selected.

Thus allowing you to do an Excel double filter on your spreadsheet, and is how to double filter in Excel on one single column.

Excel custom autofilter more than 2 criteria : Custom single or double filter
Custom single or double filter
Excel custom autofilter more than 2 criteria : Result filtered with two criteria
Result filtered with two criteria

3 - Excel advanced filter multiple criteria

To be able to do an Excel filter more than 2 criteria, you have to create another column with the exact same header as the column that you want to filter - in our example, column is name - and put one search criteria by line below. All selection criteria will be applied as an OR - meaning, results will match any of them.

Then, select the column you want to filter, select the Data menu => Advanced option in Sort & Filter box, click on Criteria range icon, and select the criteria cells - not the whole column, only the criteria data starting with same column header as the data to filter.

And voilà ! See below example, a list of hotel has been filter to all hotels from either Holiday Inn, Crowne Plaza, Radisson, or Novotel, with an advanced filter Excel 2013, resulting in an Excel custom autofilter more than 2 criteria.

The Excel custom filter more than 2 criteria contains will result in only the entries containing any of the filters being displayed.

Excel custom autofilter more than 2 criteria : Creation of a filter criteria list
Creation of a filter criteria list
Excel custom autofilter more than 2 criteria : Selection of the list to filter with multiple criteria
Selection of the list to filter with multiple criteria
Excel custom autofilter more than 2 criteria : Advanced filter for multiple criteria menu
Advanced filter for multiple criteria menu
Excel custom autofilter more than 2 criteria : Multiple criteria selection
Multiple criteria selection
Excel custom autofilter more than 2 criteria : Multiple criteria selected for text filter Excel more than two criteria
Multiple criteria selected for text filter Excel more than two criteria

See below the result with Excel multiple filters applied.

Excel custom autofilter more than 2 criteria : Excel advanced filter multiple criteria one column
Excel advanced filter multiple criteria one column

Excel custom filter multiple criteria

It is possible in Excel to filter one more than two criteria, by setting up a column with values to search, which must be named exactly as the column on which the filter must be applied.

Then, select the column to filter. Open the menu > data > filter > advanced.

Select the criteria range, the other column with the same name, and the first column will be filtered the the multiple criteria.

Similar articles

Comments (0)

Leave a comment