MO-211 Expert Prep
Module 2 · Lesson 2.13

Advanced Filter

Edit on GitHub

SQL's WHERE clause can mix any combination of AND and OR predicates, write computed expressions (WHERE total > avg(total)), and feed results into a separate destination table with INSERT INTO ... SELECT. Excel's AutoFilter can do none of those things cleanly — it is limited to a few predefined criteria per column, all combined with AND. The Advanced Filter is the tool that closes the gap: it accepts a free-form criteria range that supports OR across rows, computed conditions, and a "copy to another location" mode that writes the matching rows to a separate area without touching the original.

Part 1: When to Use Advanced Filter

Reach for Advanced Filter — instead of regular AutoFilter — when you need any of the following:

  • OR logic across columns — e.g., "Region = North or Amount > 10,000."
  • Multiple criteria sets per column — e.g., "Department = Sales with Amount > 5,000 OR Department = Marketing with Amount > 1,000."
  • Computed criteria — filter using a formula such as "Amount > average of column."
  • Copy results elsewhere — write the filtered rows to a different area of the sheet without disturbing the source list.
  • Unique-records-only output — extract distinct rows in a single operation (a non-destructive alternative to Remove Duplicates).

Part 2: Setting Up the Criteria Range

The criteria range is a small block of cells that lives anywhere on the worksheet (or on a different sheet). It must follow this structure:

  1. Row 1 — column headers that exactly match the headers in the source list (the headers you want to filter on).
  2. Row 2 onward — the criteria values, one row per condition set.

AND vs. OR Logic

  • AND — criteria placed on the same row are combined with AND.
  • OR — criteria placed on different rows are combined with OR.

Example 1 — AND across two columns

Region Amount
North >5000

Region is North AND Amount > 5000.

Example 2 — OR across two rows

Region Amount
North
>5000

Region is North OR Amount > 5000.

Example 3 — Mixed AND/OR

Department Amount
Sales >5000
Marketing >1000

(Department = Sales AND Amount > 5000) OR (Department = Marketing AND Amount > 1000).

IMPORTANT
Headers in the criteria range must match the source headers **character for character**, including spelling and trailing spaces. Copy/paste the headers from the source rather than retyping them.

Part 3: Comparison Operators in Criteria

Criteria values support standard comparison operators when typed before a number, date, or text value:

Operator Meaning Example
= Exact match (numeric or text) =North
> Greater than >5000
< Less than <100
>= Greater than or equal to >=2024-01-01
<= Less than or equal to <=2024-12-31
<> Not equal to <>Closed

A bare value with no operator behaves like "begins with" for text and "equals" for numbers/dates.

Part 4: Wildcards in Criteria

Two wildcard characters work in the criteria range:

Wildcard Meaning Example Matches
* Any number of characters Acme* Acme, Acme Corp, Acme Holdings
? Exactly one character ?at cat, bat, hat (but not chat)

To match a literal * or ?, prefix it with a tilde: ~* or ~?.

Part 5: Computed Criteria

You can use a formula as a criterion. The rules are unusual but specific:

  • The criteria-range header must be empty or use a label that does not match any source header (otherwise Excel treats the column as a regular comparison).
  • The formula evaluates against the first row of data in the source list and must return TRUE or FALSE.
  • Use relative references for the column being tested and absolute references for any range used in an aggregate.

Example: rows where Amount is above average

Source list has Amount in column D, data starts in row 2. In a free area, build:

(any non-matching header, e.g., "AboveAvg")
=D2>AVERAGE($D$2:$D$1000)

When evaluated, the formula appears as FALSE or TRUE in the criteria cell — that's normal. Excel re-evaluates it against each row in the list.

TIP
A common pattern is to leave the criteria-range header **completely blank** for computed criteria. The blank header tells Excel "this is a formula criterion, not a column match."

Part 6: Running Advanced Filter

Data tab > Sort & Filter group > Advanced.

The Advanced Filter dialog asks for:

Field Meaning
Action Filter the list, in-place (hides non-matching rows in the source) or Copy to another location (writes matches to a destination range)
List range The source data including headers — Excel pre-fills this if a cell in the list was selected
Criteria range The criteria block built in Part 2 (include the header row)
Copy to Required only when "Copy to another location" is selected — point to the top-left cell of the destination
Unique records only Drops duplicate rows from the output

Example workflow — Copy unique customer records that meet criteria

  1. Build the source list with headers in row 1.
  2. Build a criteria range with Region and Amount headers and an OR pattern.
  3. Click any cell in the source list.
  4. Data > Advanced.
  5. Choose Copy to another location.
  6. Confirm the List range auto-detected the source.
  7. Click in the Criteria range field and select the criteria block.
  8. Click in the Copy to field and click any empty cell, e.g., H1.
  9. Tick Unique records only.
  10. Click OK.

The filtered, deduped rows are written starting at H1.

IMPORTANT
When using **Copy to another location**, the destination must be on the **same worksheet** as the source list. To deliver results to a different sheet, run Advanced Filter from the destination sheet (open the dialog while that sheet is active) — Excel allows the source to be on another sheet but not the other way around.

Part 7: Removing the Advanced Filter

If you used Filter the list, in-place, restore all rows with:

Data tab > Sort & Filter group > Clear.

If you used Copy to another location, the source list was never modified — simply delete the copied range when no longer needed.

Links: