See all How-To Articles

Advanced Filter in Excel – Filter Multiple Criteria & More!

This tutorial demonstrates the capabilities of advanced filters in Excel.

 

advanced filter criteria

 

AutoFilter

Excel enables you to filter data by using its efficient Filter tool on the Ribbon or the FILTER Function (in newer versions of Excel). Many find these features very useful.

 

advanced filter single filter

 

However, the filter tool has some very specific limitations. You can filter using AND logic in a single column – so you can filter everyone who is older than 50 and whose years of service is greater than 20. Then, if you wish to filter on a second column, (service) the data has to be contained within the first filter (age).

In the graphic above, you have filtered on age (greater than 50). You can now filter once again on service, but only the service within the already filtered age criteria are available to filter on. If you then clear the filter, you can see that there is data in the service column ranging from 2 to 50. There are several ways you can filter, but some ways you cannot.

 

advanced filter clear filter

 

For example, you can use OR logic in a single column by filtering on multiple values in that column.

 

advanced filter multiple or

 

In the example above, the data is filtered to only show rows where years of service is 2, 5, or 11.

  1. To use a greater than / less than amount, use the number filter option. Select the drop down next to the heading you wish to filter. Then click Number Filters and select the relevant option – in this instance, Greater Than.

 

advanced filter number filter

 

  1. Type in the comparison value and click OK.

 

advanced filter greater than

 

This shows all rows where years of service is greater than 30.

 

advanced filter greater than 30

 

You can also use the And / Or options available in Custom AutoFilter.

The filter below returns all records where years of service is between 20 and 30 years – greater than 20 And less than 30.

 

advanced filter between 20 30

 

Whereas this one returns all records that are up to 20 Or over 30.

 

advanced filter less20 more 30

 

However, should you want to filter all the records where the service is greater than 20 or the age is greater than 50, you would not be able to use AutoFilter to do so.

Advanced Data Filter

Advanced Filter in Excel lets you set a criteria range that enables you to filter on two or more columns of data using and/or scenarios (“Filter With Multiple Criteria” example below). But first, note that it’s also a great way to extract unique data from a list with duplicates.

Extract Unique List

Excel now has a feature that enables you to remove duplicates and quickly isolate unique records. But this feature is relatively new, so alternatively, you can use advanced filters to remove duplicates in any version.

Consider the data below. Somehow, you have managed to end up with duplicates in your data. (Perhaps the entries were copy-pasted from overlapping sources.) You need to remove the duplicates to clean up and use the dataset.

 

advanced filter duplicates

 

  1. Highlight the data, and then in the Ribbon, select Data > Sort & Filter > Advanced.

 

advanced filter advanced

 

  1. In the Advanced Filter dialog, select the following:
  • Action: Copy to another Location.
  • List Range: Should already be selected – what you have highlighted.
  • Criteria Range: This should be any blank cell.
  • Copy To: A blank area of the worksheet.
  • Unique records only: Make sure this is checked.

 

advanced filter unique

 

  1. Click OK to extract unique records.

 

advanced filter unique list

 

Filter With Multiple Criteria

To filter data on multiple columns with an AND scenario, set up a criteria table for the data filter. In the graphic below, you wish to find all employees who are over 65 and have had over 5 years of service.

 

advanced filter multiple column criteria

 

Now, you can do this using AutoFilter, but let’s use advanced filters here.

  1. In the Data menu, select Sort & Filter > Advanced.
  1. Then, in the Advanced Filter dialog box, select these options:
  • Action: Select Filter the list, in-place.
  • List Range: your selected data (e.g., B2:D25 – including headings!).
  • Criteria range: make sure you have selected the headings and the values (e.g., G3:G4 as shown in the graphic below).

 

advanced filter advanced-filter criteria

 

  1. Click OK to filter the data.

 

advanced-filter advanced filter and result

 

Now, let’s try an OR filter:

 

advanced filter advanced filter or age

 

This criteria statement finds all employees who are 65 or older, or who have worked at the firm for at least 20 years.

 

advanced-filter-advanced filter or service

 

Expand your criteria to include the extra row, and then click OK.

 

advanced filter advanced filter or result

 

Create Multiple Criteria Scenarios

You can further expand your criteria to include multiple scenarios.

For this example, select all employees who are:

  • 65 and over, or
  • 60 or over and have worked for the firm for more than 5 years, or
  • 55 or over and have worked for the firm for more than 10 years, or
  • 50 or over and have worked for the firm for more than 20 years.

The criteria range should be set up thusly:

 

advanced filter advanced filter select multiple rows result

 

Click OK to implement the filters.

 

advanced filter advanced filter select multiple rows