Painless Excel custom autofilter on more than 2 criteria
Excel filter more than 2 criteria effortlessly
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 in addition with Excel wildcards to refine the filters.
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.
After having applied one of these tricks explained in that Excel help online article, you will be able to apply further advanced Excel functions on the result such as to count number of cells in result filtered on one criteria, to do an advanced vlookup in Excel with the double filtered data, or for example to count number of occurrences from the Excel advanced filter multiple criteria on one column applied.
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.
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 including eventual Excel wildcards:
- 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.
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 2013: Filtering Data - GCFLearnFree – GCFGlobal.org3 – Excel advanced filter multiple criteria one column
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.
Become an Excel Pro: Join Our Course!
Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.
Enroll HereThen, 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, also called an Excel advanced filter multiple criteria one column as the different criteria are applied simultaneously on the same column.
The Excel custom filter more than 2 criteria contains will result in only the entries containing any of the filters being displayed.
See below the result with Excel multiple filters applied.
Filtering and Sorting Data - Using Microsoft Excel - Research GuidesIt is possible to use multiple filters in Excel, with Auto Clicker Download, in order to have advancedfilter applied to an open spreadsheet, and find the Excel match multiple criteria on one or more columns.
Excel custom filter on multiple criteria
It is possible in Excel to filter on 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 Excel advanced filter multiple criteria one column will be applied on the first column, filtered with the multiple criteria.
Frequently Asked Questions
- What's the easiest way to do Excel custom filter more than 2 conditions ?
- The easiest way to filter by 2 criteria is to apply a filter in the Data - Filter menu. Using the filter, click the arrow in the column you want to filter, and in the search box, type the string you're looking for, including Excel wildcards.
- How can Excel users apply a custom autofilter to sort data based on more than two criteria without extensive manual filtering?
- Users can apply a custom autofilter for more than two criteria by utilizing the Advanced Filter feature. This involves setting up a criteria range elsewhere in the worksheet that defines all desired criteria, and then using the Advanced Filter dialog to apply these criteria to the data range.
Complete 2019 Excel for Beginners in video
Yoann Bierling is a Web Publishing & Digital Consulting professional, making a global impact through expertise and innovation in technologies. Passionate about empowering individuals and organizations to thrive in the digital age, he is driven to deliver exceptional results and drive growth through educational content creation.
Become an Excel Pro: Join Our Course!
Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.
Enroll Here