Home > Filters > Advanced > Get Started

Advanced Filter Criteria Examples

These Microsoft Excel advanced filter examples take you beyond the basic steps. See how to find top records, filter for blank cells, find records in a date range, and more examples of complex criteria. There are written steps, videos, and a sample file to download.

NOTE: In Excel 365, you can use the new FILTER Function to return a dynamic list of records based on criteria.

Author: Debra Dalgleish

Set Up Advanced Filter Criteria Range

This video shows the steps for setting up a custom filter with a criteria range, and running an Advanced Filter. The examples below show how to set up more complicated criteria, such as finding rows with blank cells.

For the written steps on setting up a basic Excel Advanced Filter, go to the Advanced Filter Introduction page.

Criteria Range Set Up Examples

The examples in this section show how to set up criteria ranges for an Excel Advanced Filter.

These filters are not as simple or intuitive as Excel's AutoFilter, which is built in to the heading cells for a named Excel table. 

However, an Advanced Filter lets you create complex filters, checking multiple columns in the source table, with and/or rules.

What is a Criteria Range?

The criteria range for an Excel advanced filter, is a group of cells on a worksheet, where you set the rules for filtering the data.

The criteria range must have a specific setup, with heading cells and criteria cells.

What is in the Criteria Range?

The criteria range must include the following cells:

• one row of cells, for heading cells
• one or more rows with criteria rules, directly below the header row
• one or more columns

What Goes in Criteria Heading Cells?

There are two types of criteria, and a different heading cell is required for each type:

• Non-Formula Criteria: For columns with criteria that is NOT a formula, the heading cell must be an exact match for the related column in the source data.
• Formula: For columns with criteria that IS a formula, the heading cell must NOT be an exact match for any column in the source data.
  • Either leave the heading cell empty,
  • Or, type a heading that is not used in any column in the original data

What Goes in Criteria Cells?

Below the heading row, at least one row with criteria is required.

In the criteria cells, you can enter the following:

• text, numbers, wildcards, operators
• formulas and cell references
• leave criteria cells blank, to indicate "no criteria"

Here is a simple criteria range example:

1. In this example, cells F1:F2 are the criteria range.
2. The heading in F1 exactly matches a column header in the database.
3. Cell F2 contains the criteria rule. The total amount must be greater than 500

More Examples

There are many more criteria range examples in the sections below, that show how, and where, to enter the criteria that you need.

Where Does the Criteria Range Go?

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

For example, the criteria range can be located:

• On the same sheet as the data
• On a different sheet from the data.
• In columns to the left or right of the data
• Directly above the data -- recommended in many beginner tutorials
• Below the data -- but I don't recommend that location.
  • It would be difficult to find the criteria in that location
  • The criteria range could be accidentally deleted.

Why Put Criteria Range Above Data?

Many beginner tutorials that I've seen rcommend putting the criteria range above the data that will be filtered.

That location is NOT required, but it does have the following advantage:

• If you filter the data in place, none of the criteria range rows will be hidden, after you apply the Advanced Filter
• With all of the criteria cells visible, you can quickly check the rules that you set

Note: This setup requires extra room at the top of the worksheet, and works best for a small criteria range.

Criteria Beside the Data

If the criteria range is set up in columns to the left or right of the data, and in some of the same rows there is the following disadvantage:

• some of the criteria cells might be hidden after you apply an "In Place" advanced filter in.

Note: The hidden criteria rows will still be applied to the advanced filter. You just won't be able to see the criteria rules in those cells.

Where Do I Put the Criteria Range?

Usually, I put the Advanced Filter criteria range to the right of the source data, with one or more blank columns between them. That way, I don't need to leave space at the top of the worksheet, which is usally "prime real estate".

The examples on this page show the criteria range to the right of the data, but you can choose whatever location you prefer.

Why Are Criteria on Multiple Rows?

If one or more criteria are in the same row of the criteria range, the Advanced Filter treats those criteria as if they are inside an AND function.

AND Rule

For example, in the screen shot below, there are 3 criteria rules in the same row. As a result, 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

If there are criteria on multiple rows, the Advanced Filter treats those criteria as if they are inside an OR function.

In this example, there are 3 rows with criteria rules, with one criterion in each row. As a result, the Advanced filter uses OR logic:

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

Note: The empty cells mean, "no criteria for this data"

AND/OR Rules

By using multiple rows, you can combine the AND and OR operators, like the criteria rules in this example:

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

Using Wildcards in Criteria

When typing criteria for a filter, you can include wildcard characters, to make the criteria rule more flexible. There are three Excel wildcard characters shown in the examples below

-- The * Wildcard (asterisk)

-- The ? wildcard (question mark)

-- The ~ wildcard (tilde)

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

The * wildcard

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

• In the screen shot below, the Customer wildcard criteria is: *o*r
• This will filter the records for any customer names that contain an "o", followed by an "r" located any number of characters after the "o".

NOTE: Upper and Lower case letters are treated equally - the filter is not case sensitive.

The ? wildcard

The question mark (?) wildcard character represents one characters in that position.

In this example any product name in the data set that

• begins with c
• followed by any single character
• then ke

will pass through the Excel advanced filter.

Only Coke and Cake are in the filtered data results shown below.

The ~ wildcard

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

For example, 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

Formulas in Advanced Filter Criteria

Most of the examples below use formulas in the criteria area. When you use a formula:

• Leave the criteria heading cell blank,
• Or, use a heading that is different from any of the table column header names

This video shows an example.

Advanced Filter Sample File

To follow along with the examples on this page, get the Excel workbook with these Advanced Filter criteria examples and sample data. The zipped workbook is in xlsx format, and does not contain any macros.

Run an Excel Advanced Filter

For all of the examples shown below, you will follow these steps, to run the Advanced Filter tool, after setting up the criteria area.

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.
  • In the screen shot below, Copy to Another Location is selected, because I want to filter data to a different sheet
• 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 the arrow at the right side of the List range box
  • --On the worksheet, 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.
• If there are duplicate records in the list, and you only want one copy of each duplicated record in the filtered results, check the option - Unique Records Only, to get a unique list of items
• Click OK, to close the Excel Advanced Filter dialog box

Filter for Top 5 Records

To get the Top 5 records with an Advanced Filter, or any other number of top records, use the LARGE function in the criteria, to find rows with the highest values.

Because there is a formula in cell F2, the criteria heading (F1) is left blank.

• F1: Criteria Heading -- Leave blank
• F2: Criteria:   =D2>=LARGE($D$2:$D$28,5)

How It Works

• The LARGE function returns the 5th largest value in the range D2:D28
  • An absolute reference is used for the data range -- $D$2:$D$28
• Cell D2, the first cell with data in the Total column, is compared to that value.
  • A relative reference is used for D2, so the total in each row is checked
• Rows with a Total greater than or equal to the 5th largest number are shown in the results
  • In the case of ties, more than five records may be returned)

Criteria Tip

Instead of typing 5 in the formula, put that number in a cell, and refer to that cell in the criteria formula.

•  =D2>=LARGE($D$2:$D$28,G2)

This makes it easy to change the "Top 5" into the "Top 10" or any other number.

Top Records After Specific Date

In this example, the filter will extract the five records with highest totals, for records that were entered on or after a specific date. If you download the sample file, there is also an example with number filters for finding the highest totals within a specific date range.

NOTE: To see the steps for showing top 10 based on additional criteria in an AutoFilter, go to my blog post on Top 10 in Filtered Rows

• H2: Type the starting date for the filtered records (Feb 3 in this example)
  I2: Type the number of top records to return (5 in this example)
  K2: COUNTIF formula calculates the number of records after the start date: =COUNTIF($A$3:$A$29,">=" & $H$2)
  M2: Formula calculates 5th largest Total for orders in data set, on or after Feb 3rd
  • =LARGE(IF($A$3:$A$29>=$H$2, $D$3:$D$29), MIN($K$2,$I$2))
• F1: Criteria Heading -- Leave blank
• F2: Criteria:   =AND(A3>=$H$2,D3>=$M$2)

How It Works

• The LARGE function returns the fifth largest value in the dataset range D3:D29, for dates on or after Feb 3rd.
  An absolute reference is used for each range, such as $A$3:$A$29.
• NOTE: This formula is arrayed enterd (Ctrl+Shift+Enter), to create a LARGE IF formula -- it returns the 5th largest total for the records IF the date is greater than Feb 3rd.
• NOTE: If there are fewer records than the Top # entered, the lower number is used -- MIN($K$2,$I$2)

In cell F2, the criteria formula checks two things:

• Cell A3, the first cell with data in the Date column, is compared to this value. A relative reference is used for this -- A3.
• Rows with a Total value that is greater than or equal to the fifth largest number, and a date on or after Feb 3rd, pass through the Excel advanced filter. (Note: In the case of ties, more than five records may be returned)
• The AND function adds a second criiteria to the filter -- the total must be greater than the minimum calculated in cell M2
  • AND(.....D3>=$M$2)

In the screen shot below, the top five records with dates on or after Feb 3rd are in the filtered results.

Filter for Matching Data in Two Columns

This Advanced Filter criteria example extracts records where there is a matching value in two columns -- the Ordered date must be equal to the Shipped date.

Here's what's in the criteria range, in column F. The heading cell is left blank, because the criterion is a formula:

• F1: Criteria Heading -- Leave blank
• F2: Criteria:   =C2=D2

In each record, the Order date is compared to the Ship date. A relative reference is used for this -- C2 and D2.

The Excel Advanced Filter extracts the rows where the two dates are equal .

Filter for Unmatched Data

To see the steps for creating an Advanced Filter for unmatched amounts in a row, please watch this short video. The written instructions are shown below.

Filter for Unmatched Data in Two Columns

This Advanced Filter criteria example extracts records where there are unmatched values in two columns -- the Invoice Amount is not equal to the Paid amount.

Here's what's in the criteria range, in column F. The heading cell is left blank, because the criterion is a formula:

• F1: Criteria Heading -- Leave blank
• F2: Criteria:   =C2<>D2

In each record, the Invoice amount is compared to the amount Paid. A relative reference is used for this -- C2 and D2. Rows where the amounts are not equal are displayed.

Note: You can use a column heading in the criteria formula, instead of a cell reference. The formula will return the #NAME? error or #VALUE! error, but the filter will work correctly. For example:

• F2: Criteria:   =Invoice<>Paid

Find Numbers within a Number

This Advanced Filter criteria example extracts records where there is an 8, anywhere within the OrderID number.

Here's what's in the criteria range, in column F. The heading cell is left blank, because the criterion is a formula:

• F1: Criteria Heading -- Leave blank
• F2: Criteria:   =ISNUMBER(FIND("8",C2))

The FIND function looks for the string "8" in the contents of cell C2.

If it finds the number, the result is the location of the number in the string. If the number is not found, an Error is the result.

Filter Rows with Blank Cells

This video shows the steps for creating an Advanced Filter for rows with blank cells, please watch this short video. The written instructions are shown below the video.

Option 1 - Filter for Blank Cells

This is the option shown in the video, above. Use these criteria range settings to filter rows with blank cells

• F1: Criteria Heading -- Leave blank
• F2: Criteria:   =C2=""

In each record, the value in column C is checked. If it is an empty string, the record passes through the Excel advanced filter.

Option 2 - Filter for Blank Cells

Use these criteria range settings to filter rows with blank cells

• F1: Criteria Heading -- Product (this is an exact match for the column heading)
• F2: Criteria:   ="="

In each record, the value in column C is checked. If the cell is empty, the record passes through the Excel advanced filter.

Filter to Match Items in a List

You can create a list of items on a worksheet, then filter another list, to show only the records that contain those items. There are 2 examples below:

1. Filter rows that have an exact match for items in the list
2. Filter rows that contain an item in the list, anywhere in the cell

For more examples, see the Filter with Criteria List page.

1. Exact Match For Items in List

The COUNTIF function is used in the Criteria range, to check each record, and test for the list items. Rows with an exact match are returned in the filter. Written instructions are below the video.

• Tip: To follow along with the video, get the Excel workbook used in the video, Filter to Match Items in List. The zipped workbook is in xlsx format, and does not contain any macros

Two criteria are used in this advanced filter

• total must be greater than 1000
• product must be in the list in column I

NOTE: To filter the results to a different sheet, start the filter from the destination sheet. See the details here.

To set up the Advanced Filter:

1. In Column I, enter the list of items that you want to match -- Bread and Cookies, in this example.
2. Enter the following values/formulas in the Criteria range:
   • F1: Criteria Heading -- "Total"
   • F2: Criteria:   >1000
   • G1: Criteria Heading -- Leave blank
   • G2: Criteria:   =COUNTIF(I:I,C2)

In each record, the value in column C is checked. If it is in the list in column I, and the total is greater than 1000, the record passes through the Excel advanced filter.

2. Contain an Item in the List

The SUMPRODUCT and COUNTIF functions are used in the Criteria range, to check each record, and test for the list items. Rows that contain an item in the list, anywhere in the Product cell, are returned in the filter.

Two criteria are used in this advanced filter

• total must be greater than 100
• product must contain an item in the list in column I, anywhere in the product cell

To set up the Advanced Filter:

1. In Column I, enter the list of items that you want to match -- Bread and Cookies, in this example.
2. Format the list as a named Excel Table (tblFind in this example)
3. Name the list of items in the table. In this example, the list is named FindList.
4. Enter the following values/formulas in the Criteria range:
   • F1: Criteria Heading -- "Total"
   • F2: Criteria:   >100
   • G1: Criteria Heading -- Leave blank
   • G2: Criteria:   =SUMPRODUCT(COUNTIF(C2,"*"& FindList &"*"))>0

In each record, the value in column C is checked. If it contains an item from the list in column I, and the total is greater than 100, the record passes through the Excel advanced filter.

Get Sample Files

Criteria Examples: Get the Excel workbook with these Advanced Filter criteria examples. The zipped workbook is in xlsx format, and does not contain any macros.

Match Items in List: Get the Excel workbook used in the video, Filter to Match Items in List. The zipped workbook is in xlsx format, and does not contain any macros.

More Filter Tutorials

Advanced Filter Introduction

Advanced Filter Criteria Slicers

Filter with Criteria List

Advanced Filter Macros

AutoFilter Basics

AutoFilter Tips

AutoFilter Macros