Home > Filters > Advanced > Examples Excel Advanced Filter IntroductionSee how to set up an Advanced Filter in Excel to create a list of unique items, or to extract specific items to a different worksheet, based on criteria. You can also use complex criteria with an Advanced Filter, so it is useful when a simple Excel AutoFilter can't do what you need. |
Create Excel Advanced FilterWatch this video to see the steps for setting up your criteria range, and running an Advanced Filter. The written instructions for all the steps are below the video. |
Create an Excel Advanced FilterHere are the steps for setting up your data, and creating an Advanced Filter in Microsoft Excel. -- 2) Set up the Criteria Range (optional) -- 3) Set up the Extract Range (optional) -- 4) Apply the Excel Advanced Filter 1. Set up the databaseTo set up the database on the spreadsheet, follow these dataset setup guidelines, so the Advanced Filter can work correctly.
|
2. Set up the Criteria Range (optional)In the criteria range for an Excel advanced filter, you can set the rules for the data that should remain visible after the filter is applied. You can use one criterion, or several, to create complex filters. WARNING: When you use text criteria for an advanced filter, Excel interprets it as "begins with". If you need an exact match for the text values, see the Exact Match example below.
After the Excel advanced filter is applied, orders with a cell value in the Total column, that is greater than $500, will remain visible. Other operators include: |
3. Set up the Extract Range (optional)If you plan to copy the data to another location (on the same sheet, or a different worksheet), you can specify the columns that you want to extract. NOTE: If you want to extract ALL columns, you can leave the extract range empty for the Excel advanced filter.
|
4. Apply the Excel Advanced FilterFollow these steps to run the Excel Advanced Filter tool: NOTE: If you want to send the data to a different sheet, follow the steps in the section Send Data to Another Sheet, below
Advanced Filter Dialog BoxWhen the Advanced Filter dialog box opens, follow these steps:
|
Filter Unique RecordsWith an Excel Advanced Filter, you can specify that you want to see unique records only. You can show the results in place, or send a copy of the unique items list to a different location. The written instructions for this technique are below the video. NOTE: There is also a Remove Duplicates tool in Excel, and a video in the next section shows how that tool works. |
Filter Unique RecordsYou can use an Excel Advanced Filter to extract a list of unique items in the database, without any duplicate records. For example,
In this example, the unique list is copied to a different location, and the original data is unchanged. Note: The original data must contain a heading, or the first item may be duplicated in the results.
Problem: Duplicates in Unique RecordsOccasionally, if you run an Advanced Filter for Unique Records Only, you might still see duplicates in the results. This can happen when:
To learn more about this problem, go to the Remove Duplicates Problem page. You'll see:
|
Video: Remove Duplicates ToolIn Excel 2007 and later versions, there is a Remove Duplicates tool. Unlike the Advanced Filter's Unique Records Only option, this does not leave the original list unchanged -- it completely removes all duplicate items from the list. Only the first instance of each item is left. This video shows how the Remove Duplicate tool works. To get the sample workbook from this video, and to see written steps, go to the Remove Duplicate Numbers page. |
Setting up Criteria RangeThese examples show how to set up criteria ranges for one criterion, or multiple criteria, using text, numbers, and wildcards. -- 1) Criteria Range Location -- 2) AND vs OR -- 3) Using Wildcards in Criteria 1) Criteria Range LocationThe examples on this page show the criteria range near the data, but that isn't required.
NOTES:
|
2) AND vs ORIf a record meets all criteria on one row in the criteria area, it will pass through the Excel advanced filter. NOTE: When you enter text criteria, Excel interprets it as "begins with". If you need an exact match, see the Exact Match example below. AND RuleIn this example, there is a single row of criteria rules. Because all criteria are in the same row, the Advanced filter uses AND logic:
OR RulesIn this example, there are 3 rows with criteria rules, with one criterion in each row. Criteria on different rows are joined with an OR operator. In the second example, the following OR logic is applied:
AND/OR RulesBy using multiple rows, you can combine the AND and OR operators, as in this example: The following AND / OR logic is applied by the Advanced Filter:
|
3) Using Wildcards in CriteriaUse wildcard characters to filter for a text string in a cell. -- a) The * Wildcard (asterisk) -- b) The ? wildcard (question mark) -- c) The ~ wildcard (tilde) a) The * wildcardThe asterisk (*) wildcard character represents any number of characters in that position, including zero characters.
In this example, the Customer wildcard criteria is: *o*r Two customer names, FoodMart and Mart-o-rama, contain an "o", followed by an "r" anywhere after the "o". The records for those two customers appear in the filtered list. b) The ? wildcardThe question mark (?) wildcard character represents one characters in that position. In this example any product name that begins with c, followed by any single character, and then ke, will pass through the Excel advanced filter. Both Coke and Cake are in the filtered results.
|
Criteria ExamplesShown below, there are two more Advanced Filter criteria examples: -- 1) Extract Items in a Range -- 2) Extract Items with Specific Text For many more examples, go to the following page -- More Advanced Filter Criteria Examples 1) Extract Items in a RangeTo extract a list of items in a range of numbers or dates, you can use two columns for one of the fields. In this example, two columns are used for the Total field. If you enter two criteria on the same row in the criteria range, you create an AND statement. In this example, any records that are extracted must be:
2) Extract Items with Specific TextWhen you use text as criteria with an Excel advanced filter, Excel finds all items that begin with that text. For example, if you type "Ice" as a criterion, Excel finds "Ice", "Ice Cream" and "Ice Milk", because they all begin with "Ice". To extract only the records that are an exact match for the criteria text, use the following format in the criteria range (upper or lower case won't affect the results):
The first equal sign starts the cell formula, and the second equal sign sets the criteria as "equal to" the text value. |
More Advanced Filter Criteria ExamplesThere are many more criteria examples on the Advanced Filters -- Complex Criteria page. For example, on that page you can see these criteria examples, and many more:
Invoice Amount Does Not Match Payment AmountThis short video shows the "unmatched data" criteria example. The written instructions are on the Advanced Filters -- Complex Criteria page, along with many other Advanced Filter criteria examples, and files to download. |
Where Should Criteria Range Be?Some Excel lessons insist that you should put the criteria range directly above the data. This is not necessary -- use the location that works best for you. The examples on this page show the criteria range near the data, but you don't need to leave it there.
NoteIf the criteria range is on the same sheet, and in the same rows as the data, the criteria cells might be hidden when you apply an "In Place" advanced filter in. The hidden criteria rows will not affect the advanced filter -- you just won't be able to see some of the criteria range. |
Using Advanced Filter MacrosAdvanced filters work well with macros too, they will help you save time, for filters that you need to run frequently. For example, use Advanced Filter macros for your weekly reports, instead of doing steps manually every week. There are notes on two of the Advanced Filter macros shown below. -- 1) Advanced Filter Macro Buttons -- 2) Advanced Filter Format & Formulas After you take a quick look at those examples, and you’re ready to try the macros, go to the sample files on the Advanced Filter Macros page, and see how to get started. That page shows you:
Advanced Filter macros run quickly, compared to other methods of moving data in Excel, so it's worth your time to use them when you can! 1) Advanced Filter Macro ButtonsFor example, here's a screen shot from one of the Advanced Filter macro sample files on the Advanced Filter Macros page. The sample file, named Send List to Sheet or File (Specific Settings), has buttons on the worksheet, so it’s easy for you to test the macros. Just click on of the worksheet buttons to run a macro, that will:
|
Using Advanced Filter MacrosAdvanced filters work well with macros too, they will help you save time, for filters that you need to run frequently. For example, use Advanced Filter macros for your weekly reports, instead of doing steps manually every week. There are notes on two of the Advanced Filter macros shown below. -- 1) Advanced Filter Macro Buttons -- 2) Advanced Filter Format & Formulas After you take a quick look at those examples, and you’re ready to try the macros, go to the sample files on the Advanced Filter Macros page, and see how to get started. That page shows you:
Advanced Filter macros run quickly, compared to other methods of moving data in Excel, so it's worth your time to use them when you can! 1) Advanced Filter Macro ButtonsFor example, here's a screen shot from one of the Advanced Filter macro sample files on the Advanced Filter Macros page. The sample file, named Send List to Sheet or File (Specific Settings), has buttons on the worksheet, so it’s easy for you to test the macros. Just click on of the worksheet buttons to run a macro, that will:
|
2) Advanced Filter Format & FormulasIn another one of the sample files for Advanced Filter Macros, named Filter & Copy Formatting, there is VBA code that copies the formatting and formulas from the original data. In the screen shot below, you can see that the extracted data has all of cell formatting, formulas, hyperlinks, and other items. This can be important, if you're extracting part of the original data, to sent to someone else. |
Get the Sample Workbooks
More TutorialsAdvanced Filters -- Complex Criteria |
Last updated: February 1, 2023 4:29 PM