Excel FILTER Function: Syntax, Versions and #SPILL! Fixes
FILTER(array, include, [if_empty]) returns only the rows where include is TRUE, and the answer spills into the cells below and to the right. It works in Excel for Microsoft 365, Excel 2024 and Excel 2021, but Microsoft does not list it for Excel 2019 or 2016. Add a third argument such as "" so that no matches does not return #CALC!.
FILTER needs Excel 2021, 2024 or Microsoft 365
Microsoft lists FILTER for Excel for Microsoft 365 on Windows and Mac, Excel 2024, Excel 2021, and Excel for iPad, iPhone, Android tablets and Android phones. Excel 2019 and Excel 2016 are not on that list. A workbook that uses FILTER shows errors in those versions.
XLOOKUP has the same limit: it is not available in Excel 2016 or 2019. If a file has to open in those versions, use another approach such as INDEX with MATCH.
The 3 arguments
The syntax is =FILTER(array, include, [if_empty]). array is the range to filter. include is a Boolean array, with the same height as array to filter rows or the same width to filter columns. if_empty is optional and is returned when nothing matches.
FILTER returns an array. When it is the final result of the formula, it spills, and you type it in one cell. If the source is an Excel table and you use structured references, the spill range resizes as data changes.
A worked example: Apples sold in the East region
Microsoft's examples use a data block A5:D20, with the product in column C, the region in column A and Units in column D. The criteria cells are H1 and H2. These are Microsoft's cell positions, so check them against your own sheet.
One condition: =FILTER(A5:D20,C5:C20=H2,"") returns every row for the product in H2, and "" if there is none. Two conditions with AND: =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"") multiplies two TRUE or FALSE arrays, so a row survives only if both are TRUE. With OR, use +: (C5:C20=H1)+(A5:A20=H2).
Add sorting by wrapping: =SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1) orders the matches on column 4 (Units) from largest to smallest. The -1 means descending.
A small test you can type in 2 minutes
Microsoft's block is large, so try the idea on 5 rows of sample values. Put Region in A1:A6 and Units in B1:B6 with headers in row 1. Rows 2 to 6 hold: East 40, West 25, East 12, North 8, East 31.
In D2 type =FILTER(A2:B6,A2:A6="East","none"). It returns 3 rows: East 40, East 12 and East 31, occupying D2:E4. Change "East" to "South" and the cell shows none, because no row matches. Now type =FILTER(A2:B6,B2:B6>20,"none"): it returns East 40, West 25 and East 31, since 12 and 8 fail the test.
Type a value into E3 while the East result is spilled and D2 shows #SPILL!, because E3 was part of the 3 by 2 spill area. Delete it and the result returns. This is the fastest way to see the first cause in the list below.
#CALC!: Excel cannot show an empty array
Microsoft says Excel does not currently support empty arrays. If FILTER finds no rows and you did not give if_empty, the cell shows #CALC!. The fix on Microsoft's #CALC! page is to change the criterion or add the if_empty argument, as in =FILTER(C3:D5,D3:D5<100,0).
Choose the replacement on purpose: "" leaves a blank-looking cell, 0 returns a number, and "No matches" tells the reader. If an include value is an error such as #N/A, or cannot be turned into TRUE or FALSE, FILTER returns an error instead.
The 7 causes of #SPILL!
FILTER needs free space for its result. Microsoft names these causes, and the first is by far the most common one with FILTER.
- Spill range is not blank: something is in the way. Click the error alert, choose Select Obstructing Cells, then clear or move it.
- Indeterminate size: the array size changes on every pass because of RAND, RANDARRAY or RANDBETWEEN.
- Extends beyond the worksheet edge: a whole-column reference like A:A returns more rows than the grid holds.
- Table formula: spilled array formulas are not supported in Excel tables, so move the formula outside the table or convert the table to a range.
- Out of memory: the array is too large, so reference a smaller range.
- Merged cells in the spill path: unmerge them or move the formula.
- Unknown: Excel could not identify the cause, so check that all required arguments are present.
Source workbooks must be open
Dynamic arrays between workbooks work only when both workbooks are open. If the source closes, refreshed formulas return #REF!. For a report that pulls from another file, keep the source open or copy the data into the report workbook.
Google Sheets FILTER works differently
Google writes FILTER(range, condition1, [condition2, ...]). Extra conditions are separate arguments, they must all be rows or all be columns, and each must match the length of the range. There is no if_empty argument: when nothing matches, Google says #N/A is returned. To filter rows and columns together, nest one FILTER inside another.
Google's examples are FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) and FILTER(A2:B10, NOT(ISBLANK(A2:A10))). A formula that works in Sheets therefore needs a rewrite for Excel: the conditions move into one include argument joined by * for AND.
Who FILTER suits, and who should wait
It suits lists that change: a sheet of 500 orders where you want one customer, or a report that should grow with new rows. It replaces manual filtering, which does not update on its own. It does not suit files shared with people on Excel 2019 or 2016.
Whole-column references like A:A are tempting but trigger the worksheet-edge #SPILL! on a grid of 1,048,576 rows. Use A5:D20-style ranges, or an Excel table. If an error is on your screen and you cannot tell which of the causes it is, Crade can read the screen and point at the blocking cell.
Questions people ask
Which Excel versions have FILTER?
Excel for Microsoft 365, Excel 2024, Excel 2021, and Excel for iPad, iPhone and Android. Microsoft does not list Excel 2019 or 2016.
Why does FILTER return #CALC!?
No rows matched and no if_empty argument was given, and Excel cannot display an empty array. Add a third argument, for example =FILTER(C3:D5,D3:D5<100,0).
How do I filter on two conditions?
Multiply the tests for AND, as in (C5:C20=H1)*(A5:A20=H2), or add them with + for OR. Both go inside the include argument.
Why is my FILTER formula showing #SPILL!?
Something blocks the cells where the result should go, most often a value or a merged cell. Use Select Obstructing Cells on the error alert. A FILTER inside an Excel table also errors, because tables do not support spilled arrays.
Does FILTER update automatically?
Yes. The result recalculates with its source. With an Excel table and structured references the spill range also resizes as data changes.