An Excel FILTER spill error usually means Excel cannot place the returned results into the cells the formula needs. Clear or move anything in the output area, unmerge any cells there, and place the formula outside an Excel Table; then check that the output has room to expand.
What a FILTER spill error means
FILTER returns a dynamic array: one formula can produce several rows or columns, and Excel fills those results into neighboring cells. If Excel cannot put the complete array into its intended area, the formula cell displays #SPILL!. The formula may be valid and its conditions may be correct; the problem can simply be that the destination cells are not available.
This is different from seeing an unexpected result within a successfully spilled array. For example, FILTER might return too many records because its condition is broad, but that alone is not a spill error. A spill error specifically signals that Excel cannot display the array in the worksheet grid as required.
FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual versions, such as Excel 2019, do not support the function. If a workbook is opened in an unsupported version, the issue is function compatibility rather than a blocked spill range.
How FILTER spills results
FILTER evaluates an array of source data and an include condition, then returns the rows or columns whose corresponding condition is TRUE. Its syntax is FILTER(array, include, [if_empty]). The optional third argument supplies a value to show when there are no matches; it does not reserve space or fix a blocked output range.
For example, with product, region, and sales in A2:C10, region names in B2:B10, and a clear destination beginning at E2, enter =FILTER(A2:C10,B2:B10="East","No matching rows") in E2. If three records match, Excel fills the results across E:G and down three rows. If the result count changes after the source data changes, the spill area can grow or shrink automatically.
The formula belongs in the top-left cell of the intended output, called the anchor cell. The cells filled by Excel are the spill range. They are not separate formulas to edit individually: changing a result cell directly is not how you change the output. Edit the formula in the anchor cell or change its source data or criteria instead.
Find the cell blocking the output
Start by selecting the cell that displays #SPILL!. Excel may show a warning icon beside it with an explanation, such as that the spill range is not blank. When available, open that warning menu and choose Select Obstructing Cells. Excel highlights cells preventing the result from appearing.
Inspect the entire intended output area, not only the first cell below the formula. A value, a space, a formula returning an empty string, or another formula can occupy a cell even if it looks blank. Delete or move the obstructing contents if they are no longer needed. If you need to preserve them, move the FILTER formula to a genuinely empty area or make room for the expected results.
Formatting alone is generally not the same as cell contents and does not normally block a spill. A cell that looks empty because its text is white, its number format hides a value, or its contents are outside the visible area may still contain something, however. Check the formula bar after selecting a suspected cell, and use the error menu or inspect the likely spill area systematically.
Common causes of #SPILL!
- Occupied cells: One or more cells in the expected result area contain data or a formula. Clear them, move them, or choose another output location.
- Merged cells: A merged cell in the spill area prevents Excel from placing results into the individual grid cells. Unmerge the affected cells, or move the formula so its output avoids them.
- Formula inside a Table: Dynamic array formulas cannot spill from within an Excel Table. Put the formula in ordinary worksheet cells outside the Table.
- Worksheet edge: The array would extend past the last available row or column. Move the formula toward the top or left of the sheet, or reduce the result size.
- Uncertain array size: Some formulas can produce an array whose dimensions change during calculation. If Excel cannot settle on a stable spill size, simplify the calculation or make its dimensions predictable.
Not every error beside FILTER is a spill error. A #SPILL! message points to the destination or output dimensions; other errors can come from the formula’s arguments, source data, or the absence of matches. Identifying the exact error text avoids trying to fix the wrong part of the workbook.
FILTER formulas and Excel Tables
Excel Tables are useful for organizing and expanding source data, and FILTER can use Table columns as its source. The limitation is where the formula is entered: a dynamic array formula cannot spill from a Table cell. This often surprises people because the source data can be in a Table while the formula itself must be outside it.
For instance, suppose a Table named SalesData has columns named Product, Region, and Sales. With a clear destination outside that Table, this formula filters its rows by region: =FILTER(SalesData,SalesData[Region]="East","No matching rows"). Enter it in a normal worksheet cell, not in a calculated-column cell within SalesData. Structured references also adjust as Table rows are added, which can make the source easier to maintain.
If the formula must appear in a Table column, consider whether a row-by-row formula is a better fit. A Table column expects a value for each row, whereas FILTER is designed to return a variable-sized array. Alternatively, place the FILTER results in a separate report area and let the Table remain the source.
Check the size and shape of the result
Make sure the output area can accommodate both the number of matching records and the number of columns in the source array. Filtering A2:C10 can return up to three columns. A match in E2 is not enough if another formula or value occupies F2, G2, or any lower cell required by the results.
Also verify that the array and include condition line up. With product names in A2:A20 and regions in B2:B20, a formula entered in D2 can return matching names with =FILTER(A2:A20,B2:B20="West","No matching rows"). The source and condition each cover 19 rows. If one range instead runs from row 2 to row 19, the include array has a different size; that is an argument problem and can produce an error other than #SPILL!.
For a wide result, check cells to the right as carefully as cells below. For a long result, make sure the formula is not so near the bottom of the worksheet that the returned rows would run past the grid. The result may be much larger than expected if the condition matches many records.
A reliable troubleshooting sequence
- Select the formula cell and read the precise error description shown by Excel, if available.
- Determine the expected output width and likely number of matching rows from the source and criteria.
- Use Select Obstructing Cells when Excel offers it. Otherwise, inspect the destination cells for values, formulas, merged cells, or another spill range.
- Clear or relocate blockers, unmerge affected cells, or move the formula to an open area outside any Table.
- Recheck the source and include ranges for matching dimensions, then confirm that the formula is entered in the top-left destination cell.
- Recalculate or edit the source data and see whether the spill now appears. If it still fails, read the updated error explanation instead of assuming the original cause remains.
Be cautious when clearing a large area: it may contain data unrelated to the FILTER formula. If the workbook is shared or the cells matter, copy the contents elsewhere or choose a new output location rather than deleting them. Moving the formula is often the quickest safe test: if it works in a blank area, the original destination was the problem.
Distinguish #SPILL! from other FILTER errors
A common confusion is #CALC! when FILTER finds no matching rows and its optional if_empty argument was omitted. That is not a blocked spill range. Add a meaningful fallback value as the third argument, such as "No matching rows", if an empty result is a normal possibility. The earlier examples include that argument for this reason.
Another possible issue is #VALUE! when the include array does not have compatible dimensions with the array being filtered. Compare the start and end rows or columns in both arguments. A mistyped range or mismatched dimensions should be corrected in the formula; clearing cells around the output will not solve it.
Finally, a blank-looking result is not necessarily an error. The formula may return empty text or values that a number format hides. Select the anchor cell to check its formula, and inspect the cells in the spill range. If the formula cell actually shows #SPILL!, focus on the output area and the message Excel provides.
Prevent future spill problems
Keep dynamic-array results in a dedicated report area, separate from manually entered notes and other formulas. Leave room for the maximum plausible number of matching rows, not just the current result. If the source grows over time, a location with open space below and to the right is less likely to become blocked later.
Keep source data in a Table if useful, but put the FILTER formula outside the Table. Use clearly sized source and criteria ranges, or structured Table references, and include an if_empty value when no matches are expected sometimes. These habits address common causes without hiding errors or forcing the result into an unsuitable layout.
Excel for Microsoft 365 on Windows, Mac, and the web supports dynamic-array behavior, though ribbon layout and error-menu presentation can vary. On Mac or Excel for the web, use the error indicator if available or inspect the output cells directly; the underlying checks—space, merged cells, Table placement, and matching dimensions—are the same.
Frequently Asked Questions
Why does my FILTER formula show #SPILL! when the cells look empty?
A cell in the required output area may contain a space, a formula, or a value hidden by formatting. Select the formula cell, use Select Obstructing Cells if Excel offers it, and inspect the highlighted cells’ contents.
Can FILTER spill inside an Excel Table?
No. Enter the dynamic-array formula in ordinary worksheet cells outside the Table. The source data may still be in a Table, and structured references can be used to refer to its columns.
Does #SPILL! mean my FILTER formula is wrong?
Not necessarily. It commonly means Excel cannot place an otherwise valid result in the destination. Check the error message and output area first; mismatched ranges or other formula issues can cause different errors.
How do I fix FILTER when there are no matching results?
If the formula shows #CALC! because no rows match, add a third argument to FILTER for the empty case, such as a short message. That handles no matches; it does not fix a #SPILL! obstruction.
Why does FILTER work in one cell but not another?
The two locations may have different amounts of free space, or one may be inside a Table, overlap a merged cell, or sit too close to a worksheet edge. Compare the complete output areas, not just the formula cells.
Related
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
- Excel Direct Access vs Link: What’s the Difference?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Drag Down Not Working? Fix the FormulaIf VLOOKUP stops working or returns the wrong result when you drag it down, check that the table…
- Excel Workbook Keeps Crashing? Fixes That Actually HelpIf an Excel workbook keeps crashing, first make a copy of the file, then open Excel in Safe…
- Excel Not Working? Fixes for Common ProblemsIf Excel is not working, first save a copy of your workbook if you can, close and reopen…
- Why Is a Hyperlink Not Working in Excel?A hyperlink in Excel usually fails because it is being clicked the wrong way, points to a moved…
- Excel Not Inserting Page Breaks? Fix the Print SetupIf Excel is not inserting a page break, first switch to View > Page Break Preview, select the…
- Excel 3D Map Not Working? Causes, Checks, and FixesIf Excel 3D Maps is not working, first confirm that you are using the Windows desktop version of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.