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
- 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.
- Go to the Data tab, select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, you need to configure as follows.
- 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.
- 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.
- Extract unique value from one column:
- In the Copy to section, specify where you want to paste the unique list.
- Check the Unique records only checkbox.
- 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
- 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.
- 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.
- Go to the Data tab and select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, you need to configure as follows.
- 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.
- In the List range section, select the entire list range A7:D17.
- In the Criteria range section, select the entire criteria range A2:D4.
- In the Copy to section, specify where you want to paste the filtered result (here I select cell F8).
- 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.
Say goodbye to manually setting up complicated criteria ranges
Unlock the power of multi-condition filtering in Excel without the complexity! Kutools for Excel's Super Filter feature offers an unparalleled ease of use that Excel's native Advanced Filter just can't match. It supports the following advanced filters with just a few clicks:
- Filter by multiple criteria in one column
- Filter by multiple criteria in multiple columns
- Filter data by text length
- Filter data based on year / month / week...
- Filter text strings by case sensitive...
Discover how Super Filter can revolutionize your workflow. Click here to download a free 30-day trial of Kutools for Excel.
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
- 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.
- For AND logic, list all criteria in the same row under their corresponding headers. For instance, If I want to filter for '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.
- Go to the Data tab and select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, you need to configure as follows.
- 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.
- In the List range section, select the entire list range A7:D16.
- In the Criteria range section, select the entire criteria range A2:D3.
- In the Copy to section, specify where you want to paste the filtered result (here I select cell F6).
- 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
- 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.
- 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.
- Go to the Data tab and select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, you need to configure as follows.
- 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.
- In the List range section, select the entire list range A7:D17.
- In the Criteria range section, select the entire criteria range A2:D4.
- In the Copy to section, specify where you want to paste the filtered result (here I select cell F8).
- 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 score higher than 90 or with a grade 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
- 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.
- 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.
- Go to the Data tab and select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, you need to configure as follows.
- 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.
- In the List range section, select the entire list range A7:D17.
- In the Criteria range section, select the entire criteria range A2:D4.
- In the Copy to section, specify where you want to paste the filtered result (here I select cell F8).
- 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
- 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.
- 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.
- Go to the Data tab and select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, configure as follows.
- 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.
- In the List range section, select the entire list range A6:B11.
- In the Criteria range section, select the entire criteria range A2:B3.
- In the Copy to section, specify where you want to paste the filtered result (here I select cell D7).
- 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.
- Go to the Data tab and select Advanced in the Sort & Filter group.
- In the Advanced Filter dialog box, configure as follows.
- In the Action section, choose the Copy to another location option.
- In the List range section, select the entire list range A7:D17.
- In the Criteria range section, select the entire criteria range A2:D4.
- 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.
- 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
- The criteria range must have column headers that exactly match those in the list range.
- If the filtered results are copied to another location, the Undo (Control + Z) function is not available.
- When applying the advanced filter in Excel, be sure to include the column headers in your selection. Omitting the headers may lead Excel to mistakenly treat the first cell in the range as a header, which could result in incorrect filtering.
- Filtered results do not update dynamically; reapply the advanced filter to refresh them after data changes.
- The following table lists the comparison operations for numbers and dates that you may use in the advanced filter criteria.
Comparison operator Meaning = Equal to > Greater than < Less than >= Greater than or equal to <= Less than or equal to <> Not equal to
Related Articles
Check if a filter is applied in a worksheet in Excel
This tutorial helps you to quickly check if a filter is applied to a specified column or to the entire worksheet without having to manually check column by column.
Paste data into filtered list only skipping hidden rows
This tutorial provides detailed steps on how to paste data into filtered rows in Excel by skipping the hidden rows.
Copy data to another worksheet with Advanced Filter
This tutorial demonstrates how to use the advanced filters in Excel to copy the results of a filter to another worksheet.
Clear filters from all worksheets in active workbook
Suppose you have created several filters in different worksheets of your Excel workbook and now you want to clear all of them at once. Usually, you need to check the filters worksheet by worksheet and then clear them manually. If you want to clear filters from all worksheets in the active workbook, try the method in this article.
Table of contents
- Advanced filter vs. normal filter
- Examples of using advanced filter
- 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
- Notes for advanced filter
- Related Articles
- The Best Office Productivity Tools
- Comments
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!