How to set up the criteria range for an Excel Advanced
Filter.

For basic information, see the introduction
to Excel Advanced Filter page.

The samples on this page assume a data layout as shown below. The database is in columns A:D and the Excel advanced filter criteria range starts in cell F1.

**Note**: When you use a formula as the Excel advanced filter
criterion, leave the heading cell blank, or use a heading that does
not match any of the table headings.

There isn't a built-in Top 10 feature for Advanced Filter, but you can use the LARGE function in the criteria, to find rows with the highest values.

F1: Criteria Heading -- Leave blank

F2: Criteria: ** =D2>=LARGE($D$2:$D$28,5)**

The LARGE function returns the fifth largest value in the range D2:D28.

An absolute reference is used for the range -- $D$2:$D$28.

Cell D2, the first cell with data in the Total column, is compared to this value. A relative reference is used for this -- D2. Rows with a Total value that is greater than or equal to the fifth largest number, pass through the Excel advanced filter. (Note: In the case of ties, more than five records may be returned)

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

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

F1: Criteria Heading -- Leave blank

F2: Criteria: ** =AND(A3>=$H$2,D3>=$M$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.

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.

Rows where the dates are equal pass through the Excel advanced filter.

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.

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? or
#VALUE! error, but the filter will work correctly. For example:

F2: Criteria: ** =Invoice<>Paid**

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.

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.

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:

- Filter rows that have an
**exact match for items**in the list - Filter rows that
**contain an item**in the list, anywhere in the cell

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:

- In Column I, enter the list of items that you want to match -- Bread and Cookies, in this example.
- 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.

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:

- In Column I, enter the list of items that you want to match -- Bread and Cookies, in this example.
- Format the list as a named Excel Table (tblFind in this example)
- Name the list of items in the table. In this example, the list is named
**FindList**. - 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.

For a zipped workbook with sample data and Excel advanced filter criteria, click here.

Last updated: November 13, 2018 11:49 AM