Home > Filters > Advanced > Examples

Excel Advanced Filter Introduction

See 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 Filter

Watch 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 Filter

Here are the steps for setting up your data, and creating an Advanced Filter in Microsoft Excel.

-- 1) Set up the database

-- 2) Set up the Criteria Range (optional)

-- 3) Set up the Extract Range (optional)

-- 4) Apply the Excel Advanced Filter

1. Set up the database

To set up the database on the spreadsheet, follow these dataset setup guidelines, so the Advanced Filter can work correctly.

1. The first row (A1:D1) has column heading label in each cell.
2. Each of the column labels in the data set must be unique
• Duplicate headings could cause problems when running an Advanced Filter.
• Tip: If possible, format your data as a named Excel table. The table will automatically prevent duplicate column headings, by adding numbers, if needed
3. Subsequent rows below the headings contain data.
4. There are no blank rows within the database.
5. There is a blank row at the end of the database, and a blank column at the right.

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.

1. In this example, cells F1:F2 are the criteria range.
2. The heading label in F1 exactly matches a column header (D1) in the database.
3. Cell F2 contains the criterion. The > (greater than) operator is used, with the number 500 (no $ sign is included).

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:

• <    less than
• <= less than or equal to
• >= greater than or equal to
• <> not equal to

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.

1. Select the cell at the top left of the range for the extracted data.
2. Type the headings for the columns that you want to extract.
   1. These must be an exact match for the column headings in the header row of the data, in spelling and punctuation
   2. Column order can be different
   3. Any or all of columns can be included.

4. Apply the Excel Advanced Filter

Follow 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

• Select any cell in the database that you want to filter
• On the Excel Ribbon, click the Data tab
• In the Sort & Filter group, click the Advanced command, to open the Advanced Filter dialog box

Advanced Filter Dialog Box

When the Advanced Filter dialog box opens, follow these steps:

• In the Action section, you can choose if Excel filters the list in place, or copy the results to another location.
• Excel should automatically detect the list range. If not, you can select the cells on the worksheet.
  • Tip: To manually select the List range cells,
  • --click on the first heading cell (cell A1),
  • --then press Shift, and click on the last data cell (cell D55)
• Select the criteria range on the worksheet
• If you are copying to a new location, select a starting cell for the copy
  • Warning: If you copy to another location:
    • ALL cells below the extract range will be cleared when the Advanced Filter is applied.
• Click OK, to close the Excel Advanced Filter dialog box

Filter Unique Records

With 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 Records

You can use an Excel Advanced Filter to extract a list of unique items in the database, without any duplicate records. For example,

• get a list of customers from an order list
• compile a list of products sold.

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.

1. Select a cell in the database.
2. On the Excel Ribbon's Data tab, click the Advanced button.
3. In the Advanced Filter dialog box, choose 'Copy to another location'.
4. For the List range, select the column(s) from which you want to extract the unique values.
5. Leave the Criteria Range blank.
6. Select a starting cell for the Copy to location.
7. Add a check mark to the Unique records only box.
8. Click OK.

Problem: Duplicates in Unique Records

Occasionally, if you run an Advanced Filter for Unique Records Only, you might still see duplicates in the results. This can happen when:

• there are numbers in the data, and
• Excel detects hidden differences between the numbers.

To learn more about this problem, go to the Remove Duplicates Problem page. You'll see:

• why the duplicates are listed (floating point precision)
• how to fix the problem by using the ROUND function.

Video: Remove Duplicates Tool

In 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.

Extract Data to Another Worksheet

With an advanced filter, you can extract data to a different sheet. Watch this video to see the steps, and the written instructions are below the video, for sending the filtered data to a separate sheet.

Video Timeline

• 0:00 Introduction
• 0:11 Sales Orders
• 0:35 Top Orders
• 1:00 Extract & Criteria
• 1:44 Advanced Filter
• 3:44 Check the Data
• 4:16 Warnings
• 4:52 Get the Excel File

Extract Data to Another Worksheet

If your Excel data is on one sheet, you can send filtered data to a different sheet, by using an Advanced Filter. In this example, the data is on Sheet1, and will be copied to Sheet2.

In Excel pull data from another sheet based on criteria, with these steps:

1. Go to Sheet2 (see the steps on the video above)
2. Select a cell in an unused part of the sheet (cell C4 in this example).
3. On the Excel Ribbon's Data tab, click Advanced
4. Choose Copy to another location.
5. Click in the List Range box
6. Select Sheet1, and select the database.
7. (optional) Click in the Criteria range box.
8. Select the criteria range
9. Click in the Copy to box.
10. Select the cell on Sheet2 in which you want the results to start, or select the headings that you have typed on Sheet2.
11. (optional) Check the box for Unique Values Only
12. Click OK, to close the dialogue box, and apply the filter

Setting up Criteria Range

These 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 Location

The examples on this page show the criteria range near the data, but that isn't required.

• The criteria range can be on the same sheet as the data, or on a different sheet.
• The criteria range can be directly above the data, or in columns to the left or right of the data.

NOTES:

• If 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.
• That will not affect the advanced filter -- you just won't be able to see some of the criteria range.

2) AND vs OR

If 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 Rule

In this example, there is a single row of criteria rules.

Because all criteria are in the same row, the Advanced filter uses AND logic:

• customer name must begin with "MegaMart"
• AND product name must begin with "Cookies"
• AND total must be greater than 500

OR Rules

In 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:

• customer name must begin with "MegaMart"
• OR product name must begin with "Cookies"
• OR total must be greater than 500.

AND/OR Rules

By 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:

• customer name must begin with "MegaMart" AND product name must begin with "Cookies"
  • OR
• product name must begin with "Cookies" AND total must be greater than 500.

3) Using Wildcards in Criteria

Use 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 * wildcard

The asterisk (*) wildcard character represents any number of characters in that position, including zero characters.

• NOTE: Because Excel interprets text criteria as "begins with", you don't need to add the * wildcard at the end of the string

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 ? wildcard

The 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.

c) The ~ wildcard

The tilde (~) wildcard character lets you search for characters that are used as wildcards.

In the first example, an asterisk is in the criteria cell -- Good*Eats -- so any customer name that begin with Good, then any characters, followed by Eats, will pass through the Excel advanced filter.

To find only the customer name that begins with Good*Eats, use a tilde character (~) in front of the asterisk in the criteria cell: Good~*Eats

Criteria Examples

Shown 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 Range

To 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:

• greater than or equal to 500
  • AND
• less than or equal to 750

2) Extract Items with Specific Text

When 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):

• ="=ice"

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 Examples

There 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:

• use formulas with Excel functions and cell reference in criteria cells
• filter top 5 records
• filter top records after specific date
• filter items that match a list
• find records where data in specific cells does not match, such as invoice amount, and amount paid
  • The video below shows this example

Invoice Amount Does Not Match Payment Amount

This 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.

• The criteria range can be on the same sheet as the data, or on a different sheet.
• The criteria range can be directly above the data, or in columns to the left or right of the data.

Note

If 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 Macros

Advanced 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:

• how to record a macro while you run an Advanced Filter
• make simple changes to the macro's VBA code
• run that macro later, to quickly apply an Advanced Filter

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 Buttons

For 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:

• Create a separate sheet with the records for each sales representative
• Or, create a separate Excel workbook with the records for each sales representative

Using Advanced Filter Macros

Advanced 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:

• how to record a macro while you run an Advanced Filter
• make simple changes to the macro's VBA code
• run that macro later, to quickly apply an Advanced Filter

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 Buttons

For 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:

• Create a separate sheet with the records for each sales representative
• Or, create a separate Excel workbook with the records for each sales representative

2) Advanced Filter Format & Formulas

In 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

• Advanced Filter Examples: Download zipped Excel advanced filter workbook with sample data and criteria. The zipped Excel file is in xlsx format, and does not contain any VBA or macros.
• Send Data to Different Sheet: Download the Excel workbook with a named table, where you can send the data to a different worksheet. The zipped Excel file is in xlsx format, and does not contain any VBA or macros.

More Tutorials

Excel FILTER function

Advanced Filters -- Complex Criteria

Advanced Filter Criteria Slicers

Advanced Filter Macros