Excel Advanced Filter: complex conditions and unique records

Normal filters can’t do “North or sales over 5,000″. Advanced Filter can — you write the conditions in cells.

Step 1: the criteria range

Above your data (leave a few empty rows), copy the column headers you want to filter on. Type the conditions under them:

• Same row = AND (North with sales over 5,000).
• Different rows = OR (North, or anything over 5,000). This layout is OR.

Step 2: run it

1. Click inside the data › Data › Advanced.
2. List range: your data including headers.
3. Criteria range: the headers plus condition rows (no empty rows below).
4. Choose Filter the list, in-place or Copy to another location › OK.

Unique records only

Tick Unique records only with no criteria range and “copy to another location” — a quick de-duplicated copy. See also unique values.

Condition tips

• North matches anything starting with North. Exact: ="=North".
• Wildcards: *pen* contains “pen”.
• Between two values: same header twice side by side, >=1000 and <=5000.

It doesn’t update

Advanced Filter is a one-time action. Change the criteria and run it again. For live results use the FILTER function.

Simpler cases: filter with multiple criteria