Contextures

Advanced Filter Criteria Examples

These examples show how to set up criteria for Excel Advanced Filters, such as filter for top records, get matching data, filter for rows with blanks, and more. Get the free sample workbook with the examples. The criteria range can be above the data, or beside it, or anywhere else in the Excel workbook.

NOTE: For the basic steps on running an Excel Advanced Filter, go to the Advanced Filter Introduction page.

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 Formulas in Advanced Filter Criteria

If you use a formula as the Excel advanced filter criterion:

  • Leave the heading cell blank,
  • Or, use a heading that is different from any of the table headings

This video shows an example.

Advanced Filter Sample Database

For the Advanced Filter criteria samples on this page, there is data in columns A:D.

  • The criteria range starts in cell F1.
  • Sometimes cell F1 is left blank, with criteria entered in cell F2.

advanced filter setup

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)

advanced filter top 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.

advanced filter top 5

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

advanced filter top date start

How It Works

  • The LARGE function returns the fifth largest value in the 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)

advanced filter criteria AND

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

advanced filter top date 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 .

advanced filter match

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

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.

advanced filter unmatched

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

F2: Criteria:   =Invoice<>Paid

Find Numbers within a Number

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.

advanced filter find number

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.

advanced filter find blank

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.

advanced filter find blank

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

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.

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. go to top

advanced filter find in list

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.

advanced filter find in list

Get the Sample File

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

More Filter Tutorials

Advanced Filter Introduction

Advanced Filter Criteria Slicers

Advanced Filter Macros

AutoFilter Basics

AutoFilter Tips

AutoFilter Programming

Last updated: June 21, 2021 8:04 PM