Excel Advanced Filter: How Criteria Ranges Really Work

Kelbrook Fasteners sells fixings, fasteners and site consumables out of a trade counter and two vans in Nelson, Lancashire. 214 open accounts, 840 open invoices at the end of June 2026, £1,318,470 outstanding.

The credit rule is four years old and fits in a sentence: an account goes on hold when it has an invoice more than 45 days overdue with more than £500 on it. It is run at month end from a sheet called Ledger — one row per open invoice, with Account, Customer, Division, Days overdue, Balance and Invoice date across the top — by way of Data → Sort & Filter → Advanced:

• List range: Ledger[#All]
• Criteria range: Criteria, a named range pointing at $A$1:$D$4 on a sheet called Rules
• Copy to: Hold!$A$1
• Unique records only: ticked

Forty seconds. The accounts that come out go into CreditHold.csv, which the ERP swallows at 06:00 on the first of the month and turns into credit blocks. It had worked every month since 2022.

On 24 June 2026 the Export division was moved onto a trade credit insurer, and Export stopped being Kelbrook's problem to chase. So the person who looked after the rules sheet took Export out of the criteria: clicked row 4, dragged across A4:D4, pressed Delete.

The cells emptied. The row stayed. Criteria still pointed at $A$1:$D$4.

On 30 June the extract was run. It returned all 840 invoices, Unique records only turned those into all 214 accounts, and at 06:00 on 1 July every account Kelbrook had went on credit hold.

• 41 of the 176 wrongly-held accounts tried to buy something in the first four working days of July. £47,300 of orders were refused at the counter and on the phone.
• £31,900 of that came back later in the month. £15,400 did not.
• Two goodwill credits of £250 went out, and unwinding the ERP took two people most of 6 July.

The list was not obviously wrong, and that is the part worth sitting with. 214 rows of account numbers under a header that says Account is exactly what a correct credit-hold list looks like. It is only too long if you know what month-end normally looks like, and the person who imports the CSV does not.

While that was being unwound, somebody opened the tab next door. Watchlist — everything over 30 days, all divisions, built the same way with Copy to another location — was last run on 29 May. An Advanced Filter extract has no link to the data it came from, so it had sat there through the whole of June looking precisely as current as the day it was made. Nine accounts crossed thirty days in June and were never on it. £41,260 went unchased. £18,400 of that was Prestwood Site Services, which was at 34 days and still answering the phone in the middle of June, and which went into administration on 14 July.

£15,400 of lost orders, £500 of credits, £18,400 written off, 176 customers who had to be apologised to, and not one error message.

Advanced Filter is not fragile. It did what it was told, twice. What it does not do — ever — is say anything about itself: not what the criteria said, not how many rows matched, not when the output was taken.

1) The Criteria Range as Excel Read It

Two facts about that table explain the whole morning.

The rows are ORed. An invoice comes out if it matches row 2, or row 3, or row 4.

Row 4 has nothing in it, and a record filter with no conditions in it is not a filter that matches nothing. It matches everything. So the union of "47 Trade invoices", "14 Contract invoices" and "all 840 invoices" is 840 invoices, and the tick in Unique records only turned that into a tidy list of 214 account numbers.

Microsoft's own documentation says it outright: be careful not to include blank rows in the criteria range, because a blank row means all records will be returned. The dialog box does not say it, the result does not say it, and nothing goes red.

🎯 Scenario: Open your own criteria range and look at what the name actually spans. Press Ctrl+G → Special → Current region on the criteria block, or just type =ROWS(Criteria) and =COLUMNS(Criteria) in two spare cells. If ROWS is bigger than the number of condition rows you can see, you are already returning everything and have been since the day the range was widened.

2) Same Row Is AND, Separate Rows Are OR

This is the whole grammar of a criteria range, and it is geometry rather than syntax:

That last line is the one that trips experienced people. A condition you type on row 2 applies to row 2 only. If you add a second division as a new row and type only the division name, you have just said "everything in Contract, at any age, for any amount", and your list grows in a way that looks like the business changed rather than the rule.

Repeat every condition on every row, or use a computed criterion (section 7) so there is only one row to get wrong.

And the direction of travel is worth naming: adding cells to a row narrows the result; adding a row widens it. Every accident in this article is a widening.

🎯 Scenario: Write your criteria range out as a sentence with "and" and "or" in it, then read it to the person who owns the rule. If the sentence has more "or"s in it than they expected, the range is not the rule they think they have.

3) The Criteria Headers Are Matched to the Data by Text

Excel does not know that column C of your criteria is the days column because it is third. It knows because the header cell above it says Days overdue and a field in the list range says Days overdue too.

So the headers matter, exactly:

• A trailing space in Days overdue is not the same text.
• A renamed ledger column — Days past due, after a tidy-up — is not the same text.
• Case does not matter. DAYS OVERDUE is fine.

And here is the part that makes a mismatch quiet rather than loud. Microsoft's rule for a computed criterion is that its header must be blank, or a label that is not one of the column labels in the list. That is the same test, read from the other side: a header that matches no field is not an error, it is the signature of a formula criterion. So a mistyped header does not stop the filter. It stops being a condition on that column.

The fix costs one keystroke each. Make the criteria headers formulas that point at the data headers:

A1: =Ledger[[#Headers],[Division]]
B1: =Ledger[[#Headers],[Days overdue]]
C1: =Ledger[[#Headers],[Balance]]

Rename a ledger column now and the criteria headers rename themselves. They cannot drift apart, because there is only one copy of the text.

🎯 Scenario: Put =COUNTIF(Ledger[#Headers],A1) under each criteria header. Every one of them must return 1. A zero is a criterion that is no longer about the column you think it is about, and it will never announce itself.

4) Text Criteria Are Begins-With, Not Equals

Type Ash in a criteria cell and Advanced Filter returns Ashford Retail, Ashby Fixings and ASHTON GROUP LTD. Criteria text is a begins-with match, and it is case-insensitive.

Most of the time that is convenient. It is a disaster when one customer's name is a prefix of another's, which in a trade ledger is constant: Hart brings back Hartsmere Joinery and Hartley & Sons; Bell brings back Bell Plumbing and Bellingham Contracts.

To ask for equals, you have to say so twice:

="=Ash"

That is a formula returning the text =Ash, which is the criteria syntax for an exact match. Typing =Ash directly does not work — Excel reads it as a reference to a cell or name called Ash.

The rest of the text vocabulary:

Case sensitivity is not available in ordinary criteria at all. If you need it, it is a computed criterion with EXACT.

🎯 Scenario: Take the longest customer name in your criteria and sort your customer list by name. Look at the name immediately after it. If it starts with the same letters, your filter has been quietly including it, and a begins-with match on a customer name is the single most common reason an extract has rows in it nobody can account for.

5) Numbers, Blanks and Dates

Numbers are the easy half: >45, >=500, <0, <>0. Two things will still get you.

Text that looks like a number matches nothing. If Days overdue arrived from an export as text, >45 returns no rows at all — not an error, no rows. The tell is a left-aligned column, and =COUNT(Ledger[Days overdue]) against =COUNTA(Ledger[Days overdue]) is the two-cell version: if COUNT is lower, some of them are text, and VALUE or a multiply-by-one fixes the column.

Dates are read in the sheet's own regional order. >=01/03/2026 is 1 March on a UK machine and 3 January on a US one, and a criteria range typed by a colleague in another office is a genuine hazard. The durable version puts the date in a cell and uses a computed criterion:

=F2>=$H$1

F2 is the first data row of Invoice date — relative, so it walks down the rows. $H$1 holds a real date — absolute, so every row is compared against the same one. A criterion written that way survives being opened in any locale, and the date it uses is visible in a cell instead of buried in the rules sheet.

🎯 Scenario: If a criteria range in your workbook contains a typed date, move it into a cell today and replace the criterion with a comparison. While you are there, write the date's meaning next to it — "invoices from this date onwards" — because >=01/03/2026 in a criteria cell does not say which side of the comparison it is on.

6) The List Range Stops at a Blank Row

When you open the Advanced Filter dialog, Excel pre-fills the list range from the current region, and a current region ends at the first completely blank row. A spacer row someone left above a subtotal, or a row that went empty when an invoice was cleared, truncates the list range at that point.

The filter then runs, correctly, on the rows above the gap. It reports nothing, because as far as it is concerned that was the list.

Two habits remove the problem permanently:

1. Make the ledger a Table (Ctrl+T) and use Ledger[#All] as the list range. A Table's extent is a definition, not a guess; it grows with the data, and a blank row inside it does not end it.
2. Never use a spacer row. Use a bottom border.

🎯 Scenario: Select one cell in your data and press Ctrl+Shift+* (Current region). Whatever highlights is what Advanced Filter will offer you as the list range. If that is not all of your data, fix the data, not the dialog.

7) Computed Criteria: One Formula Instead of Four Columns

A computed criterion is a criteria cell holding a formula that returns TRUE or FALSE, evaluated once per record. It is the answer to nearly every criteria-range problem in this article, because it collapses a grid of cells into one cell you can read.

Three rules, all of them load-bearing:

1. The header above it must be blank, or text that is not a field name. A header matching a field turns the cell back into an ordinary criterion on that column.
2. References to the list use the first data row, relatively. C2, not C:C and not C1. Excel walks the formula down the records the way it walks a fill-down.
3. References to anything outside the list are absolute. $H$1, always.

Kelbrook's whole rule, in one cell under an empty header:

=AND(OR(C2="Trade",C2="Contract"),D2>45,E2>500)

There is no second row to forget a condition on, and no third row to leave empty. The row that caused the incident cannot exist in this design, because the design has one row.

Computed criteria are also the only route to several tests ordinary criteria cannot express:

A caution worth the sentence: a computed criterion is a formula sitting in a cell, so it displays TRUE or FALSE for whichever row it was written against, and that value means nothing. People delete it thinking it is broken. Put a note beside it.

🎯 Scenario: Rewrite your widest criteria range as a single computed criterion and keep both for a month. Run the filter each way and compare the counts with =COUNTA(). When they agree twice, delete the grid.

8) Copy to Another Location Is a Snapshot, Not a View

This is the fault that cost Kelbrook the most money, and it is not a bug. It is the feature working as designed, in a way that looks like something else.

Copy to another location writes values once. The output has no relationship to the list range, no relationship to the criteria, no refresh, no connection in Queries & Connections, and nothing anywhere on the sheet recording when it was made. It is a paste. If the ledger changes a minute later, the extract does not, and it does not look any different from an extract taken a minute ago.

Watchlist was five weeks stale on a sheet where five-week-stale and five-minute-fresh are visually identical. Nobody was careless. There was nothing to see.

Four more things about the destination, all of which bite:

• It must be on the active sheet. Filtering data on Ledger and copying to Hold means standing on Hold when you open the dialog and pointing the list range back at Ledger. Do it the other way round and Excel says You can only copy filtered data to the active sheet.
• One destination cell means all columns. Select a single cell and every field in the list range comes out, in the list's order.
• A row of destination headers means those fields, in that order. Type the field names you want at the destination, select that row as the Copy to, and Advanced Filter extracts exactly those. Misspell one and you get The extract range has a missing or invalid field name — which, unlike the criteria headers, is a real error message you cannot miss.
• Whatever is under the destination is in the firing line. The output is as many rows as matched this time, which is not how many matched last time. Put nothing beneath an extract, and give it its own sheet.

The staleness has a two-cell fix. At the moment you run the filter, record what you filtered:

Hold!H1: 30/06/2026                      (typed by hand, the run date)
Hold!H2: =ROWS(Ledger)                    (converted to a value, Ctrl+C then Paste Values)
Hold!H3: =ROWS(Ledger)-H2                 (live: how much the ledger has moved since)

H3 is zero on the day and nonzero forever after. It is the only thing on that sheet that knows the extract is old.

🎯 Scenario: Find every Copy-to extract in your workbooks and ask one question of each: if the data changed this morning, what on this sheet would look different? If the answer is nothing, put the run date and the row count next to it before you close the file.

9) Unique Records Only Dedupes the Columns You Extract

The tick box means unique records, and a record is however many fields you asked for. Extract one column and you get its distinct values. Extract all six and two invoices are duplicates only if all six cells agree, which on a ledger is never, so the box does nothing and looks broken.

Kelbrook extracts Account alone, which is why 840 invoices arrived as 214 rows. The tick box did its job perfectly on the wrong input, and the deduplication is what made the output believable: 840 rows might have raised an eyebrow. 214 did not.

Worth knowing alongside it:

• Unique records only works with filter in place too, hiding duplicate rows rather than copying distinct ones.
• It is case-insensitive, like everything else here.
• Data → Remove Duplicates changes your data; Advanced Filter writes a separate list and leaves the ledger alone. When someone asks for "a list of the accounts", it is almost always the second one they want.
• =UNIQUE() does the same thing and recalculates, which is the subject of the next section.

🎯 Scenario: Count the columns in your destination header row, then say out loud what "unique" now means. If you extracted Account and Invoice, unique is per invoice, and every row is unique — the tick box is decoration.

10) The Formula That Cannot Go Stale

Everything Kelbrook's extract does, a formula does live:

=SORT(UNIQUE(FILTER(Ledger[Account],
  (Ledger[Days overdue]>45)*(Ledger[Balance]>500)*
  ((Ledger[Division]="Trade")+(Ledger[Division]="Contract")))))

* is AND, + is OR, and the three conditions are visible as text in one cell rather than as geometry across four. It recalculates the moment the ledger does, so it cannot be five weeks old, and there is no row to leave blank.

So what is Advanced Filter still for?

• Filter in place, for printing or eyeballing without copying anything anywhere.
• A frozen snapshot on purpose — the hold list as sent, kept as evidence of what was sent.
• Criteria that a non-formula colleague can edit. Typing Contract under a header is a genuinely lower bar than editing a FILTER, and this matters more than formula people like to admit.
• Reordering and subsetting columns on the way out, which the destination header row does for free.
• Excel 2019 and earlier, where FILTER and UNIQUE do not exist.

The decision rule that survives contact with a real office: if the output is read, use FILTER. If the output is sent, use Advanced Filter and stamp it with the date.

🎯 Scenario: Build the FILTER twin next to your extract and leave both in place. Put =COUNTA(extract)-COUNTA(twin) between them. It reads zero while the extract is current and starts counting the day it goes stale — which is the alarm the snapshot has never had.

11) Seven One-Cell Checks

Each of these is one cell, and between them they would have caught everything that went wrong on 30 June.

1  =ROWS(Criteria)                      How many rows the name spans. Count your conditions against it.
2  =COUNTIF(F2:F4,0)                    With F2 = COUNTA(A2:D2) filled down: empty criteria rows. Must be 0.
3  =COUNTIF(Ledger[#Headers],A1)        Each criteria header matches a field. Must be 1.
4  =COUNTIFS(Ledger[Division],"Trade",Ledger[Days overdue],">45",Ledger[Balance],">500")
     +COUNTIFS(Ledger[Division],"Contract",Ledger[Days overdue],">45",Ledger[Balance],">500")
                                        The invoices the rule selects, from the ledger. 61.
5  =COUNTA(UNIQUE(FILTER(Ledger[Account],(Ledger[Days overdue]>45)*(Ledger[Balance]>500)
     *((Ledger[Division]="Trade")+(Ledger[Division]="Contract")))))
                                        The accounts behind them, live. 38.
6  =COUNTA(Hold!A:A)-1                  What the extract actually produced. Must equal check 5.
7  =(COUNTA(Hold!A:A)-1)/COUNTA(UNIQUE(Ledger[Account]))
                                        Share of accounts on hold. 18% is a month end. 100% is an incident.

Checks 4 and 5 are the important pair, and it is worth being precise about why. They compute the same rule with COUNTIFS and FILTER, from the data, without going near Advanced Filter — so they are not checks on the extract, they are a second opinion about the answer. On 30 June check 5 said 38 and the extract said 214, and one number disagreeing with another is the only kind of error this feature will ever hand you.

Check 7 is the one that needs no thought and no context. A credit-hold list holding 100% of the ledger's accounts is not a number anybody has to interpret.

Add =ROWS(Ledger)-Hold!$H$2 from section 8 and you have the staleness alarm as well, which is what Watchlist never had.

🎯 Scenario: Put checks 5, 6 and 7 in three cells above the headers of the sheet your extract lands on, and colour them. Three cells. Then run your own month end and read them before you send anything to anybody.

12) Twelve Traps

1. A blank row in the criteria range returns every record. The rows are ORed and an empty row has no conditions, which everything satisfies. Clearing cells is not deleting a row.
2. A criteria range named wider than the conditions in it has blank rows by definition. =ROWS(Criteria) is the whole test.
3. Same row is AND, separate rows are OR, and a condition is not inherited by the row below. Repeat every condition on every row.
4. Criteria headers are matched to fields by text. A trailing space or a renamed column does not error — it stops being a condition on that column, because a non-matching header is how you declare a computed criterion.
5. Text criteria are begins-with and case-insensitive. Hart catches Hartsmere and Hartley. ="=Hart" is equals; EXACT in a computed criterion is case.
6. >45 matches nothing when the column is text. Compare COUNT with COUNTA before you believe an empty result.
7. Typed dates are read in the sheet's regional order. Put the date in a cell and compare against it.
8. The list range stops at the first completely blank row. Use a Table and Ledger[#All], and never use spacer rows.
9. Computed criteria need a blank header, a relative reference to the first data row, and absolute references to everything else. Get one wrong and the criterion silently changes meaning.
10. Copy to another location is a paste. No refresh, no link, no date. Stamp it with the run date and the row count or it will be read as current forever.
11. The destination must be on the active sheet, a single destination cell extracts every column, and a destination header row extracts exactly those fields — misspelled, that one does error.
12. Unique records only dedupes the fields you extracted. One column means distinct values; six columns means nothing at all, and the tick box looks broken when it is being obeyed.

The lesson Kelbrook took out of July was not that Advanced Filter is dangerous, and they did not replace it. The hold list is still an extract, because the hold list is sent to an ERP and a frozen record of what was sent is worth having.

What changed is that three cells now sit above it: the count the rule produces from the ledger, the count the extract produced, and the difference. And the criteria range became one computed criterion under one empty header, so there is no second row to clear and no fourth row to leave behind.

The underlying point is not about this feature. It is that a criteria range is a range of cells, not a statement of intent. It says what it contains at the instant you press OK, and an empty row is a perfectly valid thing to contain — a filter with no conditions in it, which every record in the world satisfies. Excel will not ask you whether you meant that, on the morning of the first, at six o'clock, with the ERP already reading the file.