How to use Excel advanced filter - A full guide with examples

Excel's Advanced Filter is a powerful tool that provides flexibility beyond the standard filtering functionality, enabling users to perform complex filtering tasks efficiently. This guide takes an in-depth look at Excel's Advanced Filters feature, compares it to regular filters, provides practical examples, and offers considerations for optimal use. It will empower you to master the use of advanced filters with confidence.

• Extract a unique list
• Filter in one column with multiple criteria (match any criteria)
• Filter in multiple columns with multiple criteria
  • With AND logic (match all criteria)
  • With OR logic (match any criteria)
  • With AND as well as OR logic
• Advanced filter with wildcard
• Extract only certain columns

Advanced filter vs. normal filter

The main distinction between Excel's normal filter and the Advanced Filter lies in their complexity and functionality. While the normal filter offers straightforward, single-column criteria-based filtering within the original data set, the Advanced Filter extends beyond these limits by:

• Allowing the use of multiple criteria across various columns.
• Providing the capability to extract unique values from a dataset.
• Enabling the use of wildcards for more flexible, partial matching.
• Permitting the extraction of filtered data to a separate location.

Examples of using advanced filter

This section will provide different practical examples to show you how to use advanced filters in Excel to achieve different filtering effects.

Extract a unique list

Excel's Advanced Filter can quickly generate a list of unique values from a dataset, a task that can be cumbersome with normal filters. If you have a list of sales transactions with duplicate rows and wish to extract a list of unique rows, the Advanced Filter feature in Excel can simplify this task. Please follow the instructions below to accomplish this.

1. Go to the "Data" tab, select "Advanced" in the "Sort & Filter" group.
   
2. In the "Advanced Filter" dialog box, you need to configure as follows.
   1. In the "Action" section, choose an option you need. Since I want to locate the unique list to a different place, I choose the "Copy to another location" option.
   2. Specify the List range section:
      • Extract unique value from one column:
        Select the column that contains the values from which you want to extract unique entries. For example, to extract the unique customer names in this case, select A1:A11.
      • Extract unique rows based on multiple columns:
        Select the range that includes all the columns you’re considering. In this case, as I want to extract unique rows based on Customer names, Sales and Region, I select the entire range A1:C11.
   3. In the" Copy to" section, specify where you want to paste the unique list.
   4. Check the "Unique records only" checkbox.
   5. Click the "OK" button. See screenshot:
      

Result

As shown in the screenshot below, unique rows are extracted from the original data range.

Filter in one column with multiple criteria (match any criteria)

Filtering data in one column with multiple criteria allows you to display rows that meet any of your specified conditions. This can be particularly useful when you're working with large datasets and need to narrow down the information based on several potential matches. Here's how you can achieve this using Excel's Advanced Filter feature:

Step 1: Prepare Your Original List Range Data

Ensure your list range dataset has clear column headers, as these will be important for setting up the criteria range. Here, I am using the following table of student scores as an example.

Step 2: Criteria Range Setup

1. In the range above or apart from the list range, create your criteria range. The headers you type in the criteria range must exactly match those in the list range to work correctly. Here my criteria range is located above the list range.
2. Beneath the header, list down each of the criteria you want to match. Each criterion should be in its own cell, directly below the previous one. This setup tells Excel to match any of these criteria.
   In this example, I am looking for students with “scores greater than 95 or less than 60” so that I can effectively filter the list range to include both high and low-scoring students. Therefore, I enter each criterion in separate rows under the Score header. The entire criteria range is shown below:
   

Step 3: Apply the Advanced Filter

Now you can apply the advanced filter to accomplish the task as follows.

1. Go to the “Data” tab and select “Advanced” in the “Sort & Filter” group.
   
2. In the "Advanced Filter" dialog box, you need to configure as follows.
   1. In the "Action" section, choose an option you need. Here as I want to locate the filtered result to a different place, I choose the "Copy to another location" option.
   2. In the "List range" section, select the entire list range A7:D17.
   3. In the "Criteria range" section, select the entire criteria range A2:D4.
   4. In the "Copy to" section, specify where you want to paste the filtered result (here I select cell F8).
   5. Click "OK" to apply the filter. See screenshot:
      

Result

Then you can see that only the rows where the "Score" column matches any of the criteria (>95 or <60) are extracted.

Filter in multiple columns with multiple criteria

Having covered filtering with multiple criteria in a single column, we now turn our attention to multi-column filtering. This section will guide you through applying multiple criteria across different columns using AND, OR, and combined AND/OR logic.

• To apply AND logic, put the criteria on the same row.
• To apply OR logic, position the criteria on separate rows.

With AND logic (match all criteria)

Filtering data in multiple columns with multiple criteria using AND logic means that each row must meet all specified criteria across different columns to be displayed. Here's how to accomplish this with Excel's Advanced Filter:

Step 1: Prepare Your Original List Range Data

Ensure your list range dataset has clear column headers, as these will be important for setting up the criteria range. Here, I am using the following table of student scores as an example.

Step 2: Criteria Range Setup

1. Create your criteria range either above or separate from the list range by typing headers that exactly match those in the list range. Here my criteria range is located above the list range.
2. For AND logic, list all criteria in the same row under their corresponding headers. For instance, if I want to filter "Class A" students with scores over 85, then the criteria range should be set like this:
   

Step 3: Apply the Advanced Filter

Now you can apply the advanced filter to accomplish the task as follows.

1. Go to the "Data" tab and select "Advanced" in the "Sort & Filter" group.
   
2. In the "Advanced Filter" dialog box, you need to configure as follows.
   1. In the "Action" section, choose an option you need. Here as I want to locate the filtered result to a different place, I choose the "Copy to another location" option.
   2. In the "List range" section, select the entire list range A7:D16.
   3. In the "Criteria range" section, select the entire criteria range A2:D3.
   4. In the "Copy to" section, specify where you want to paste the filtered result (here I select cell F6).
   5. Click "OK" to apply the filter. See screenshot:
      

Result

In the result, only rows that match all criteria across the specified columns will be displayed or copied. In our example, only students from class A with scores above 85 are extracted.

With OR logic (match any criteria)

To filter data in multiple columns using OR logic (matching any of the criteria) in Excel's Advanced Filter, follow these steps:

Step 1: Prepare Your Original List Range Data

Ensure your list range dataset has clear column headers, as these will be important for setting up the criteria range. Here, I am using the following table of student scores as an example.

Step 2: Criteria Range Setup

1. Create your criteria range either above or separate from the list range by typing headers that exactly match those in the list range. Here my criteria range is located above the list range.
2. With OR logic, place each set of criteria for the same column on separate rows, or list each criterion on separate rows beneath its corresponding header. For instance, If I want to filter for students with scores higher than 90 or grades of F, the criteria range should be set like this:
   

Step 3: Apply the Advanced Filter

Now you can apply the advanced filter to accomplish the task as follows.

1. Go to the "Data" tab and select "Advanced" in the "Sort & Filter" group.
   
2. In the "Advanced Filter" dialog box, you need to configure as follows.
   1. In the "Action" section, choose an option you need. Here as I want to locate the filtered result to a different place, I choose the "Copy to another location" option.
   2. In the "List range" section, select the entire list range A7:D17.
   3. In the "Criteria range" section, select the entire criteria range A2:D4.
   4. In the "Copy to" section, specify where you want to paste the filtered result (here I select cell F8).
   5. Click "OK" to apply the filter. See screenshot:
      

Result

This will filter your data based on the criteria specified, matching any criteria listed. If a row matches any of the criteria across the columns you specified, it will be included in the filtered results.

In this case, the filter will return only students whose scores are higher than 90 or who have a grade of F.

With AND as well as OR logic

To filter data in multiple columns with a combination of AND as well as OR logic using Excel's Advanced Filter, you can follow these steps.

Step 1: Prepare Your Original List Range Data

Ensure your list range dataset has clear column headers, as these will be important for setting up the criteria range. Here, I am using the following table of student scores as an example.

Step 2: Criteria Range Setup

1. Create your criteria range above or beside your list range. Include the column headers that match those in the list range exactly. Here my criteria range is located above the list range.
2. Under the headers, enter the criteria using a combination of AND and OR logic.
   • For AND logic, criteria from different columns should be placed on the same row.
   • For OR logic, criteria should be placed on separate rows.
   • For combined AND-OR logic, organize each set of OR conditions in separate blocks of rows. Within each block, place AND criteria on the same row.
     For instance, to filter students in Class A with scores greater than 90, or in Class B with a grade of B, set the criteria range as follows:
     

Step 3: Apply the Advanced Filter

Now you can apply the advanced filter to accomplish the task as follows.

1. Go to the "Data" tab and select "Advanced" in the "Sort & Filter" group.
   
2. In the "Advanced Filter" dialog box, you need to configure as follows.
   1. In the "Action" section, choose an option you need. Here as I want to locate the filtered result to a different place, I choose the "Copy to another location" option.
   2. In the "List range" section, select the entire list range A7:D17.
   3. In the "Criteria range" section, select the entire criteria range A2:D4.
   4. In the "Copy to" section, specify where you want to paste the filtered result (here I select cell F8).
   5. Click "OK" to apply the filter. See screenshot:
      

Result

Excel will display only the rows that meet your complex criteria combination.

In this example, the advanced filter will only return students with scores greater than 90 in Class A or students with a grade of B in Class B.

Advanced filter with wildcard

Using wildcards with Excel's advanced filter allows for more flexible and powerful data searches. Wildcards are special characters that represent one or more characters in a string, making it easier to filter for text patterns. Here are detailed instructions on how to use the advanced filter with wildcards in Excel.

Step 1: Prepare Your Original List Range Data

Ensure your list range dataset has clear column headers, as these will be important for setting up the criteria range. In this example, let's say you have a list of names and some of the names you are looking for follow a specific naming pattern.

Step 2: Criteria Range Setup

1. Create your criteria range above or beside your list range. Include the column headers that match those in the list range exactly. Here my criteria range is located above the list range.
2. Below the header, enter the criteria using wildcards.
   • *: Represents any number of characters and can be used before, after, or within a string.
   • ?: Represents a single character at a specific position.
   In this example, I want to filter names that start with the character “J”, so I enter J* under the Name header of the criteria range. See screenshot:
   

Step 3: Apply the Advanced Filter

Now you can apply the advanced filter to filter all names starting with the character J.

1. Go to the "Data" tab and select "Advanced" in the "Sort & Filter" group.
   
2. In the "Advanced Filter" dialog box, configure as follows.
   1. In the "Action" section, choose an option you need. Here as I want to locate the filtered result to a different place, I choose the "Copy to another location" option.
   2. In the "List range" section, select the entire list range A6:B11.
   3. In the "Criteria range" section, select the entire criteria range A2:B3.
   4. In the "Copy to" section, specify where you want to paste the filtered result (here I select cell D7).
   5. Click "OK" to apply the filter. See screenshot:
      

Result

The advanced filter will display only those rows from the Name column where the names begin with the letter "J", adhering to the pattern specified by the wildcard in the criteria range.

Extract only certain columns

Using Excel's advanced filter to extract only certain columns is particularly useful for analyzing large datasets where you only need to focus on certain information.

Assume your dataset is in the range A7:D17, and you want to filter this data based on criteria specified in B2:D4 and extract only the Name, Score and the Grade columns. Here's how to do it.

Step 1: Specify the Columns to Extract

Below or next to your dataset, write the headers of the columns you wish to extract. This defines the "Copy to" range where the filtered data will appear. In this example, I type the Name, Score and Grade headers in the range F7:H7.

Step 2: Apply the Advanced Filter

Now you can apply the advanced filter to filter only certain columns based on specified criteria.

1. Go to the "Data" tab and select "Advanced" in the "Sort & Filter" group.
   
2. In the "Advanced Filter" dialog box, configure as follows.
   1. In the "Action" section, choose the "Copy to another location" option.
   2. In the "List range" section, select the entire list range A7:D17.
   3. In the "Criteria range" section, select the entire criteria range A2:D4.
   4. In the "Copy to" section, select the range (F7:H7 in this case) where you've written the headers of the columns you want to extract.
   5. Click "OK" to apply the filter. See screenshot:
      

Result

You can see that the extraction result includes only the specified columns.

Notes for advanced filter

Related Articles