FILTER Function Generator
Return every row of a range that meets your condition, as a spilled dynamic array.
FILTER
Return every row of a range that meets your condition, as a spilled dynamic array.
The rows and columns you want returned. Do not include the header row.
A test that produces TRUE/FALSE for each row, with the same number of rows as the array.
What to show when nothing matches. Excel only - Google Sheets FILTER has no if_empty argument, so for Sheets delete this third argument from the copied formula and wrap the result in IFERROR.
=FILTER($A$2:$C$100, $C$2:$C$100>1000, "No results")Worked Example
An orders table sits in A2:C100 with the order amount in column C, and you want every order over 1,000.
=FILTER($A$2:$C$100, $C$2:$C$100>1000, "No results")Returns: Spills all three columns of every row whose column C value exceeds 1000 into the cells below and right of the formula, or shows "No results" if no order qualifies.
Checks Before You Paste
- •#SPILL! means the results have nowhere to land. Clear the cells below and to the right of the formula, and note that dynamic arrays cannot spill inside an Excel Table - move the formula outside the Table.
- •The include test must have exactly as many rows as the array (or as many columns, for a horizontal filter). $A$2:$C$100 with a test over $C$2:$C$50 returns #VALUE!, and full-column references on one side only are the usual cause.
- •Combine conditions with arithmetic, not AND/OR: multiply for AND - ($C$2:$C$100>1000)*($B$2:$B$100="West") - and add for OR. AND() and OR() collapse the whole array to a single TRUE/FALSE and quietly return everything or nothing.
How FILTER works
Availability: Excel 2021, Microsoft 365 and Excel for the web. Excel 2019 and earlier do not have dynamic arrays at all and show _xlfn._xlws.FILTER with #NAME?. Google Sheets has a FILTER of its own that takes conditions as separate arguments and has no if_empty.
FILTER returns every row of a range that satisfies a test, as a live result that spills into the cells below and to the right of the formula. It is the formula equivalent of the AutoFilter dropdowns, with two differences that matter: the output is a separate copy that leaves the source untouched, and it updates by itself whenever the source data changes.
The second argument is not a criterion in the SUMIF sense - it is an array of TRUE and FALSE values, one per row, and FILTER keeps the rows whose entry is TRUE. That is why the include test has to cover exactly the same number of rows as the array being filtered, and why conditions are combined with arithmetic rather than with the AND and OR functions. Multiply for AND, add for OR: ($C$2:$C$100>1000)*($B$2:$B$100="West") keeps rows that satisfy both, and ($B$2:$B$100="Open")+($B$2:$B$100="Pending") keeps rows satisfying either. AND and OR collapse an entire array into one TRUE or FALSE, so a formula using them returns either everything or nothing.
When no row matches, FILTER returns #CALC! unless you supply the third argument, which is what to show instead. "No results" or "" both work, and giving it is nearly always right for a formula anyone else will look at. When the results have nowhere to spill - something already occupies the cells they need, or the formula sits inside an Excel Table, which cannot host a spill - the result is #SPILL! instead. Clearing the blocking cells or moving the formula outside the Table fixes it.
The output behaves as one object. You cannot edit or delete a single cell of it; Excel refuses with a message about changing part of an array. Other formulas can refer to the whole spilled result with the # operator, as in =ROWS(F2#) or =SUM(G2#), which keeps them correct as the number of matching rows changes. FILTER also composes well with the rest of the dynamic array family - SORT, UNIQUE, TAKE and CHOOSECOLS all take its output directly.
Syntax
=FILTER(array, include, [if_empty])| Argument | Required | What it does |
|---|---|---|
array | Required | The rows and columns to return. Usually the data without its header row. It can be a single column, several columns, or a whole block, and every column of a matching row comes back. |
include | Required | A boolean array with the same number of rows as array (or the same number of columns when filtering horizontally). Normally a comparison over one column, optionally several comparisons combined with * for AND and + for OR. |
if_empty | Optional | What to return when no row matches. Omit it and an empty result is reported as #CALC!. Text in quotes, "" for a blank, or a number all work. |
More worked examples
An orders table in A2:D500 with the region in column B and the order date in column D. You want this year's West orders.
=FILTER($A$2:$D$500, ($B$2:$B$500="West")*($D$2:$D$500>=DATE(2026,1,1)), "No matching orders")Returns: Spills all four columns of every row that is both in the West and dated on or after 1 January 2026, or shows "No matching orders" when none qualify.
The multiplication is the AND. Replacing it with AND() would test the two ranges as a whole and return every row or none.
A ticket list in A2:C300 where the status in column B can be Open, Pending, Closed or Cancelled, and you want the two active states.
=FILTER($A$2:$C$300, ($B$2:$B$300="Open")+($B$2:$B$300="Pending"), "Nothing outstanding")Returns: Returns the rows whose status is either Open or Pending. A row cannot match both conditions, so the addition never produces a value above 1.
If overlapping conditions were possible, the sum could reach 2, which FILTER still treats as TRUE - any non-zero number counts.
Pulling one customer's invoice amounts out of a wide table and showing the largest first. Customer names are in column A, amounts in column C.
=SORT(FILTER($C$2:$C$500, $A$2:$A$500=$F$1), 1, -1)Returns: With Acme in F1 and four Acme invoices, spills those four amounts down four cells, largest first.
Change F1 and the whole block redraws. Add =SUM(G2#) beside it and the total follows the spill however many rows it has.
Common mistakes
- #CALC! when nothing matches
- An empty result is an error, not an empty cell. Supply the third argument - "None" or "" - and the formula reports the empty case cleanly. This shows up most often on a filter driven by a criteria cell that has just been cleared.
- #VALUE! from a mismatched include range
- The test must be exactly as long as the array. $A$2:$C$100 filtered by $C$2:$C$50 fails, and so does using a whole-column reference on one side only. After inserting rows, check both ends of both ranges; converting the source to a Table and referring to its columns removes the problem entirely.
- Using AND or OR to combine conditions
- AND($B$2:$B$100="West", $C$2:$C$100>1000) reduces both ranges to a single TRUE or FALSE, so FILTER either returns every row or falls to if_empty. There is no error to notice. Multiply the conditions for AND and add them for OR.
- #SPILL! with nothing visible in the way
- The usual causes are a stray space in a cell inside the spill area, a merged cell in the range, or the formula sitting inside an Excel Table, which cannot contain a spilled result. Select the formula cell and use Go To Special for blanks or constants below it, or move the formula out of the Table.
- Comparing against an empty criteria cell
- $B$2:$B$500=$F$1 with F1 empty matches only the genuinely blank rows, so the result looks broken rather than unfiltered. If an empty criteria cell should mean "show everything", write the test as ($F$1="")+($B$2:$B$500=$F$1).
Frequently Asked Questions
That no row satisfied the include test and no if_empty argument was supplied, so there is nothing for the formula to return. Add a third argument such as "No results" or "". It is not a sign of a broken formula - an empty result is a legitimate answer that FILTER has no way to express without help.
Multiply the conditions for AND and add them for OR: ($B$2:$B$500="West")*($C$2:$C$500>1000) keeps rows meeting both, ($B$2:$B$500="West")+($B$2:$B$500="East") keeps rows meeting either. Excel treats TRUE as 1 and FALSE as 0 during arithmetic, so multiplying gives 1 only where both are true.
Yes - wrap it: =SORT(FILTER(array, include), 2, -1) sorts the filtered rows by their second column, descending. UNIQUE, TAKE and CHOOSECOLS nest around it the same way, which is how most dynamic array reports are assembled.
Because the spilled block is a single array produced by one formula in the top-left cell, and Excel does not allow part of an array to be changed. Edit or delete that top-left formula to change the whole result. To keep a static copy, copy the block and paste it as values somewhere else.
Yes, it recalculates like any other formula, so edits to existing rows appear immediately. New rows appear only if they fall inside the ranges you referenced - which is why pointing FILTER at an Excel Table, whose columns expand as rows are added, is usually better than a fixed range.
Related Tools
Filter Excel Rows
Do the same filtering on an uploaded file, without opening Excel or writing a formula.
UNIQUE Generator
The dynamic array partner most often wrapped around a FILTER result.
Sort Excel Online
Sorting a whole sheet, when the ordering matters more than the subset.
XLOOKUP Generator
For when you want a single matching value rather than every matching row.