Available in:Excel 2021 or later (Works in Excel 2021, every later version, Microsoft 365 and Excel for the web)Category:Dynamic arrays

FILTER

FILTER in Dutch Excel

Returns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: E3 holds the formula
Row	A	B	C	E	F	G
1	Entry	Journal	Amount	Journal	MEM
2	25-0412	INK	€3,025.00€ 3.025,00
3	25-0413	MEM	€15,000.00€ 15.000,00	25-0413=FILTER(A2:C7, B2:B7=F1)=FILTER(A2:C7; B2:B7=F1)	MEM	€15,000.00€ 15.000,00
4	25-0414	VRK	€6,050.00€ 6.050,00	25-0415	MEM	€2,500.00€ 2.500,00
5	25-0415	MEM	€2,500.00€ 2.500,00	25-0417	MEM	€48,000.00€ 48.000,00
6	25-0416	BNK	€9,680.00€ 9.680,00
7	25-0417	MEM	€48,000.00€ 48.000,00

=FILTER(A2:C7, B2:B7=F1)

=FILTER(A2:C7; B2:B7=F1)

What FILTER does and when to use it

What it does: it returns every row of a table that meets your condition, as a list that spills into the cells below, for example all manual journal entries from a full auditfile.

When to use it: for lists that must follow the data, such as overdue invoices or the rows behind a dashboard number. It replaces copying rows by hand after an AutoFilter, and XLOOKUP would return only the first match.

Three main gains:

Always up to date: the list changes with the data, with no filter to reapply and no copying.
Hides nothing: the source rows stay visible and untouched.
Feeds other functions: wrap it in SORT or SUM to sort or total the result.

How Excel evaluates it

Excel works through these steps:

it evaluates include first, producing one TRUE or FALSE (or a number) per row
it checks that this array is as tall as array (or as wide, when you keep columns); if not, the result is #VALUE!
it walks down array and keeps each row whose include value is TRUE or non-zero, in the original order
if at least one row is kept, the block spills from the formula cell, as many rows tall as were kept and as wide as array
if none is kept, it returns if_empty

If any cell in the spill area is already occupied, you get #SPILL! instead of the list.

Watch out: If no row passes, FILTER returns #CALC! unless you give it if_empty.

Mental model

A sieve with one hole per row: include decides which holes are open, and whatever falls through lands, in order, below the formula.

Terms used on this page: spill, array, cell

Syntax

Official FILTER documentation

FILTER(array, include, [if_empty])

FILTER(array; include; [if_empty])

Arguments
Argument	Type	Meaning
`array`	Required	The rows you want back: one column or a whole table.
`include`	Required	One TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.
`if_empty`	Optional	What to show when no row passes; without it you get #CALC!.

Return value:The rows of array where include is TRUE, or if_empty when there are none

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

List every manual journal entry

You are testing journal entries at Veldhuis Installatie BV. The XAF auditfile is in a sheet with one row per entry. Manual entries in journal MEM carry more fraud risk than automatic ones, so you want them on one list. Type another journal code in F1 and the list refills itself.

Sheet excerpt: E3 holds the formula
Row	A	B	C	E	F	G
1	Entry	Journal	Amount	Journal	MEM
2	25-0412	INK	€3,025.00€ 3.025,00
3	25-0413	MEM	€15,000.00€ 15.000,00	25-0413=FILTER(A2:C7, B2:B7=F1)=FILTER(A2:C7; B2:B7=F1)	MEM	€15,000.00€ 15.000,00
4	25-0414	VRK	€6,050.00€ 6.050,00	25-0415	MEM	€2,500.00€ 2.500,00
5	25-0415	MEM	€2,500.00€ 2.500,00	25-0417	MEM	€48,000.00€ 48.000,00
6	25-0416	BNK	€9,680.00€ 9.680,00
7	25-0417	MEM	€48,000.00€ 48.000,00
8

Formula cell (E3)InputSpilled result

Formula in E3

=FILTER(A2:C7, B2:B7=F1)

=FILTER(A2:C7; B2:B7=F1)

Result: 25-0413 | MEM | €15,000.00 / 25-0415 | MEM | €2,500.00 / 25-0417 | MEM | €48,000.0025-0413 | MEM | € 15.000,00 / 25-0415 | MEM | € 2.500,00 / 25-0417 | MEM | € 48.000,00

Step by step

Excel reads F1 and finds MEM.
It evaluates include, B2:B7=F1, by comparing each of the 6 journal cells with MEM: FALSE, TRUE, FALSE, TRUE, FALSE, TRUE. That array is 6 rows tall, the same as A2:C7, so the sizes match.
It walks down A2:C7 and keeps the rows where include is TRUE: the second, fourth and sixth, which are rows 3, 5 and 7 of the sheet: 25-0413, 25-0415 and 25-0417.
Three rows of three columns come back, so the result spills from E3 to G5: entry numbers in E, journal in F, amounts 15,00015.000, 2,5002.500 and 48,00048.000 in G. A thin blue border marks the spill range.
Type BNK in F1 and the block shrinks to one row: 25-0416, BNK, 9,6809.680. Type a code that does not occur, such as KAS, and you get #CALC!, because no row passes and there is no if_empty yet.

Watch out: If anything is typed in E3:G5, the formula shows #SPILL! instead of the entries. Keep the cells below and beside a FILTER empty.

More on this example: why it works, 1 variation

Why it works

Comparing a range with one cell gives one TRUE or FALSE per row, and that array is exactly the shape FILTER wants for include. Because array and include cover the same 6 rows, the third TRUE belongs to the third row, and the kept entries come back in the order they appear in the auditfile.

Variations

=FILTER(A2:A7, B2:B7=F1)

=FILTER(A2:A7; B2:B7=F1)

Return only the entry numbers: make array one column wide; include stays the journal test.

Intermediate

Overdue receivables to confirm, with a message when none qualify

From the receivables aging list of Veldhuis Installatie BV you want the customers whose invoice is more than a set number of days overdue, as input for confirmation requests and the provision test. The threshold sits in F1 so you can change it. One invoice has no overdue figure because its due date was never entered. When no customer qualifies, the list should say so instead of showing an error.

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Invoice	Customer	Days overdue	More than (days)	9090
2	25-1101	Kaya Design BV	1212	Confirm	Van Dijk Transport BV=FILTER(B2:B7, C2:C7>F1, "No customers to confirm")=FILTER(B2:B7; C2:C7>F1; "No customers to confirm")
3	25-1102	Molenaar Bouw BV			Hendriks Metaal BV
4	25-1103	Van Dijk Transport BV	124124		Smits Bouw BV
5	25-1104	Hendriks Metaal BV	9191
6	25-1105	Mulder Horeca BV	9090
7	25-1106	Smits Bouw BV	210210
8

Formula cell (F2)InputSpilled result

Formula in F2

=FILTER(B2:B7, C2:C7>F1, "No customers to confirm")

=FILTER(B2:B7; C2:C7>F1; "No customers to confirm")

Result: Van Dijk Transport BV / Hendriks Metaal BV / Smits Bouw BVVan Dijk Transport BV / Hendriks Metaal BV / Smits Bouw BV

Step by step

Excel reads F1: 90. It compares each cell of C2:C7 with it: 12>90 is FALSE, the blank C3 counts as 0 so 0>90 is FALSE, 124>90 is TRUE, 91>90 is TRUE, 90>90 is FALSE, 210>90 is TRUE.
array is only B2:B7, the customer column, so FILTER keeps the third, fourth and sixth customers: Van Dijk Transport BV, Hendriks Metaal BV and Smits Bouw BV.
The three names spill from F2 to F4. Hendriks Metaal BV is on the list with 91 days and Mulder Horeca BV is not with exactly 90, because the test is 'more than 90'; with >= both would be on it.
Change F1 to 365. No invoice is more than 365 days overdue, so include is FALSE six times. Because if_empty is supplied, F2 shows No customers to confirm instead of #CALC!.
Without the third argument, that same change would leave #CALC! in F2, and any formula that refers to F2 would inherit the error.

Watch out: The blank in C3 silently counts as 0. For this test that hides the invoice without a due date, which you still have to follow up with the client, and the same blank would pass a test like C2:C7<10.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

array and include do not have to be the same width, only the same height: the test runs on column C while the answer comes from column B. if_empty is only used when every row fails, so a normal file gives the full list and a clean aging list gives a readable message.

More to watch out for

If the overdue days arrive as text from an export, Excel treats text as larger than any number, so every comparison such as C2:C7>F1 is TRUE and the list shows every customer, including those only 12 days late. Check one cell with ISNUMBER.

Variations

=FILTER(A2:C7, C2:C7>F1, "No customers to confirm")

=FILTER(A2:C7; C2:C7>F1; "No customers to confirm")

Return the invoice number and days as well: widen array to A2:C7 and keep the same test.

Advanced

Manual entries above performance materiality, largest first

In journal entry testing you review manual entries of Veldhuis Installatie BV that are at or above performance materiality. Journal code and threshold sit in H1 and H2. The list must show entry, journal, account and amount, sorted with the largest amount on top, so you test those first.

Sheet excerpt: G4 holds the formula
Row	A	B	C	D	G	H	I	J
1	Entry	Journal	Account	Amount	Journal	MEM
2	25-0501	MEM	45004500	€12,500.00€ 12.500,00	Performance materiality	90009000
3	25-0502	VRK	80008000	€8,400.00€ 8.400,00
4	25-0503	MEM	11001100	€4,300.00€ 4.300,00	25-0504=SORT(FILTER(A2:D8, (B2:B8=H1)(D2:D8>=H2)), 4, -1)=SORT(FILTER(A2:D8; (B2:B8=H1)(D2:D8>=H2)); 4; -1)	MEM	40004000	€18,750.00€ 18.750,00
5	25-0504	MEM	40004000	€18,750.00€ 18.750,00	25-0501	MEM	45004500	€12,500.00€ 12.500,00
6	25-0505	INK	45004500	€15,200.00€ 15.200,00	25-0506	MEM	16001600	€9,900.00€ 9.900,00
7	25-0506	MEM	16001600	€9,900.00€ 9.900,00
8	25-0507	BNK	11001100	€21,000.00€ 21.000,00
9

Formula cell (G4)InputSpilled result

Formula in G4

=SORT(FILTER(A2:D8, (B2:B8=H1)*(D2:D8>=H2)), 4, -1)

=SORT(FILTER(A2:D8; (B2:B8=H1)*(D2:D8>=H2)); 4; -1)

Result: 25-0504 | MEM | 4000 | €18,750.00 / 25-0501 | MEM | 4500 | €12,500.00 / 25-0506 | MEM | 1600 | €9,900.0025-0504 | MEM | 4000 | € 18.750,00 / 25-0501 | MEM | 4500 | € 12.500,00 / 25-0506 | MEM | 1600 | € 9.900,00

Step by step

Excel starts with the first test, B2:B8=H1, journal equals MEM: TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE.
The second test, D2:D8>=H2, amount at least 9,0009.000: 12,50012.500 TRUE, 8,4008.400 FALSE, 4,3004.300 FALSE, 18,75018.750 TRUE, 15,20015.200 TRUE, 9,9009.900 TRUE, 21,00021.000 TRUE.
Multiplying the two arrays turns TRUE into 1 and FALSE into 0, row by row: 1×1=1, 0×0=0, 1×0=0, 1×1=1, 0×1=0, 1×1=1, 0×1=0. Only rows where both tests pass give 1, which is AND logic.
FILTER keeps the first, fourth and sixth rows of A2:D8: 25-0501 (12,50012.500), 25-0504 (18,75018.750) and 25-0506 (9,9009.900), in source order.
SORT receives that 3-row block and sorts it on its 4th column, amount, with sort_order -1, descending: 18,75018.750, 12,50012.500, 9,9009.900. The result spills from G4 to J6 with 25-0504 on top.
Set H2 to 25000. No manual entry is that large, so FILTER has nothing to return and gives #CALC!; SORT passes the error on, and G4 shows #CALC!. The pitfalls explain why adding if_empty inside SORT is not the fix it looks like.

Watch out: Using + where you meant *, (B2:B8=H1)+(D2:D8>=H2), silently turns AND into OR: you get every manual entry plus every large entry in other journals, 6 rows instead of 3, and the list still looks plausible.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Each comparison yields an array with one answer per row, and multiplication combines them position by position, so a row survives only if every factor is 1. SORT works on whatever FILTER hands it, which is why the column number 4 refers to the fourth column of the filtered block, not to column D of the sheet.

More to watch out for

A journal code exported as MEM with a trailing space drops out of the list without any error. If an entry you expect is missing, compare LEN of the two cells.
Adding if_empty inside, FILTER(A2:D8, ..., "No entries"), works in a normal file but fails in an empty one: SORT then receives a single text and is asked to sort on a fourth column that does not exist, which gives #VALUE!. Test the count first, as the Master example does.

Variations

=SORT(FILTER(A2:D8, (B2:B8=H1)+(D2:D8>=20000)), 4, -1)

=SORT(FILTER(A2:D8; (B2:B8=H1)+(D2:D8>=20000)); 4; -1)

OR logic on purpose: every manual entry, plus any entry of 20,00020.000 or more in another journal (25-0507 joins the list).

=SORTBY(FILTER(A2:C8, (B2:B8=H1)*(D2:D8>=H2)), FILTER(D2:D8, (B2:B8=H1)*(D2:D8>=H2)), -1)

=SORTBY(FILTER(A2:C8; (B2:B8=H1)*(D2:D8>=H2)); FILTER(D2:D8; (B2:B8=H1)*(D2:D8>=H2)); -1)

Sort by amount without showing it: SORTBY takes a separate key, here the filtered amounts. Longer, so prefer it only when the key column must stay hidden.

Master

Purchase postings around year end, chosen columns only

For the cut-off test at Veldhuis Installatie BV you list the postings in journal INK between 20 December 2025 and 10 January 2026. The auditfile sheet has five columns, but your workpaper needs only entry, account and amount. The formula must name its parts for the next reviewer, handle a window without postings cleanly, and pick columns without retyping the table in a different order.

Sheet excerpt: H5 holds the formula
Row	A	B	C	D	E	H	I	J
1	Entry	Date	Journal	Account	Amount	From	20 Dec 2025
2	25-0871	12 Dec 2025	INK	45004500	€3,200.00€ 3.200,00	To	10 Jan 2026
3	25-0885	18 Dec 2025	BNK	16001600	€5,400.00€ 5.400,00	Journal	INK
4	25-0893	23 Dec 2025	INK	40004000	€1,850.50€ 1.850,50
5	25-0902	29 Dec 2025	INK	45004500	€7,425.00€ 7.425,00	25-0893=LET( data, A2:E9, posted, B2:B9, journal, C2:C9, keep, (posted >= I1) * (posted <= I2) * (journal = I3), IF(SUM(keep) = 0, "No postings in this window", FILTER(FILTER(data, keep), {1,0,0,1,1})) )=LET( data; A2:E9; posted; B2:B9; journal; C2:C9; keep; (posted >= I1) * (posted <= I2) * (journal = I3); IF(SUM(keep) = 0; "No postings in this window"; FILTER(FILTER(data; keep); {1\0\0\1\1})) )	40004000	€1,850.50€ 1.850,50
6	25-0905	30 Dec 2025	VRK	80008000	€12,100.00€ 12.100,00	25-0902	45004500	€7,425.00€ 7.425,00
7	25-0911	3 Jan 2026	INK	45004500	€2,640.00€ 2.640,00	25-0911	45004500	€2,640.00€ 2.640,00
8	26-0003	7 Jan 2026	MEM	45004500	€980.00€ 980,00
9	26-0012	14 Jan 2026	INK	40004000	€4,100.00€ 4.100,00
10

Formula cell (H5)InputSpilled result

Formula in H5

=LET(
  data, A2:E9,
  posted, B2:B9,
  journal, C2:C9,
  keep, (posted >= I1) * (posted <= I2) * (journal = I3),
  IF(SUM(keep) = 0, "No postings in this window", FILTER(FILTER(data, keep), {1,0,0,1,1}))
)

=LET(
  data; A2:E9;
  posted; B2:B9;
  journal; C2:C9;
  keep; (posted >= I1) * (posted <= I2) * (journal = I3);
  IF(SUM(keep) = 0; "No postings in this window"; FILTER(FILTER(data; keep); {1\0\0\1\1}))
)

Result: 25-0893 | 4000 | €1,850.50 / 25-0902 | 4500 | €7,425.00 / 25-0911 | 4500 | €2,640.0025-0893 | 4000 | € 1.850,50 / 25-0902 | 4500 | € 7.425,00 / 25-0911 | 4500 | € 2.640,00

Step by step

LET names the pieces: data is A2:E9, posted is B2:B9, journal is C2:C9. Nothing is calculated yet.
keep multiplies three tests per row. Posted on or after 20 Dec 2025: 0, 0, 1, 1, 1, 1, 1, 1. On or before 10 Jan 2026: 1, 1, 1, 1, 1, 1, 1, 0. Journal is INK: 1, 0, 1, 1, 0, 1, 0, 1.
Row by row the products are 0, 0, 1, 1, 0, 1, 0, 0. 25-0871 is too early, 26-0012 too late, 25-0885 is a bank entry, 25-0905 a sales entry and 26-0003 a memorial entry. keep holds 1 for 25-0893, 25-0902 and 25-0911.
SUM(keep) is 3, not 0, so IF takes the FILTER branch. The inner FILTER(data, keep) returns the three kept rows with all five columns.
The outer FILTER gets {1,0,0,1,1} as include. It is one row and five columns wide, the same width as the block, so FILTER keeps columns instead of rows: entry, account and amount, and drops date and journal.
The result spills from H5 to J7: 25-0893, 4000, 1,850.501.850,50; 25-0902, 4500, 7,425.007.425,00; 25-0911, 4500, 2,640.002.640,00. Set I2 to 22 Dec 2025 and no INK posting falls in the window, SUM(keep) is 0, and H5 shows No postings in this window.

Watch out: The mirror-image mistake is adding the tests instead of multiplying them. (posted >= I1) + (posted <= I2) + (journal = I3) is at least 1 for every row here, so all 8 postings come back, and a cut-off list that should show 3 rows looks merely busy, not wrong.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

include accepts a horizontal array as wide as the data, and then FILTER selects columns; nesting a row filter inside a column filter picks both at once. The explicit SUM(keep) = 0 test matters because if_empty on the inner FILTER would hand a single text to the outer one, whose five-column include no longer fits, and the result would be #VALUE!.

Do not use this construction when the file must open in Excel 2019 or earlier, or when the report is a one-off: there, Advanced Filter (Data, Advanced, copy to another location) produces the same rows as static values, at the cost of re-running it by hand whenever the source changes.

More to watch out for

If the To date should include the whole last day but the source holds date and time values, posted <= I2 drops postings made after midnight on 10 Jan. Use posted < I2 + 1 for date-time data.
FILTER over whole columns, such as A:E, makes Excel test about a million rows on every recalculation. Point it at the exact range or, better, a Table that grows with the data.

Variations

=FILTER(A2:E9, (B2:B9>=I1)*(B2:B9<=I2)*(C2:C9=I3), "No postings in this window")

=FILTER(A2:E9; (B2:B9>=I1)*(B2:B9<=I2)*(C2:C9=I3); "No postings in this window")

Without LET and the column filter: all five columns, and if_empty is safe here because nothing is nested around it.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#VALUE! or a wrong list with AND or OR	You combine two conditions with AND(B2:B8=H1, D2:D8>=H2) and get #VALUE! or a wrong result instead of the list.	AND and OR squash a whole array into one TRUE or FALSE, but the include argument needs one TRUE or FALSE per row, so FILTER returns #VALUE! or a wrong result. Multiply the tests for AND, (B2:B8=H1)*(D2:D8>=H2), and add them for OR, so each row keeps its own answer.
#CALC! on days when nothing matches	A report built on a FILTER breaks on the days when no row matches.	Supply if_empty, for example a text such as No matches, or an empty text if the cell should look blank.
#SPILL! although the formula is correct	The result is #SPILL! even though the formula is correct.	Something already sits in the cells the list needs, often a forgotten value or a space. Click the error, choose Select Obstructing Cells, and clear them.
#VALUE! when array and include differ in height	array is `A2:C50` but include is `B2:B49`, and the result is #VALUE!.	Both must cover exactly the same rows. A Table with structured references keeps them in step when rows are added.

Used in combinations

All invoices of one supplier in one cell with TEXTJOIN and FILTERPick the invoice numbers of one supplier with FILTER and join them into a single line with TEXTJOIN, with a message when the supplier has no invoices.Excel 2021 or later (This combination needs Excel 2021 or later)
Invoices missing from the ledger with FILTER, ISNA and XMATCHList the invoices on the receivables specification that are not in the general ledger: XMATCH looks each one up, ISNA turns every miss into TRUE, and FILTER keeps those rows, with a message when nothing is missing.Excel 2021 or later (This combination needs Excel 2021 or later)
The five largest manual entries with TAKE, SORT and FILTERList the five largest manual (MEM) journal lines for journal entry testing: FILTER keeps the MEM lines, SORT puts the largest amount first and TAKE keeps the first five rows.Excel 2024 or later (This combination needs Excel 2024 or later)
Year-end cut-off test with FILTER and SUMFlag the sales invoices whose booking date and delivery date fall on different sides of year end and total them: two comparisons with the year-end date give TRUE or FALSE per date, FILTER keeps the invoices where the two differ, and SUM adds them up.Excel 2021 or later (This combination needs Excel 2021 or later)
Differences above the reporting threshold with LET, FILTER, HSTACK and ABSReconcile the ledger to the client's specification per customer and list only the differences above the reporting threshold: LET names the differences once, HSTACK pairs them with the customer, FILTER keeps the rows where ABS exceeds the threshold.Excel 2024 or later (This combination needs Excel 2024 or later)
Duplicate invoice numbers across two exportsStack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.Excel 2024 or later (This combination needs Excel 2024 or later)
Entries posted on weekends and public holidays with FILTER, WEEKDAY and COUNTIFList the journal entries dated on a Saturday, a Sunday or a Dutch public holiday: WEEKDAY flags the weekends, COUNTIF checks the date against a holiday list, and FILTER keeps the flagged rows.Excel 2021 or later (This combination needs Excel 2021 or later)
Find the customer with a regex lookupPull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.Excel for Microsoft 365 (This combination needs Excel for Microsoft 365)
Gap test on invoice numbers with FILTER, SEQUENCE and XMATCHFind the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.Excel 2021 or later (This combination needs Excel 2021 or later)
Journal entries where debit does not equal creditList the entries whose debit and credit totals differ: take the unique entry numbers, total debit and credit per entry with SUMIFS, and keep the unequal ones with FILTER over HSTACK, all named in one LET.Excel 2024 or later (This combination needs Excel 2024 or later)
Round-sum journal entries with FILTER, MOD, ROUND and ABSFlag ledger lines whose amount is an exact multiple of 1,000 (and above a minimum): ABS removes the sign, ROUND clears floating-point noise, MOD tests the multiple and FILTER lists the lines.Excel 2021 or later (This combination needs Excel 2021 or later)