An Excel FILTER spill error usually means the formula’s results cannot fit in the cells where Excel wants to display them. Clear or move anything blocking the spill range, unmerge cells in that area, or move the formula outside an Excel table; then check that the formula has matching ranges and an available place to return its results.
What an Excel FILTER spill error means
FILTER is a dynamic array function: one formula can return several rows or columns, and Excel places those results in neighboring cells automatically. The cell containing the formula is the starting point, often called the anchor cell. The surrounding cells that receive the results make up the spill range.
If Excel cannot place the results, it displays #SPILL! in the anchor cell. This is generally a placement problem, not proof that the filtering condition itself is wrong. Select the formula cell to see the intended spill range, which Excel outlines with a dotted border. That outline helps identify where results need to go.
FILTER is available in Microsoft 365 and Excel 2021 or later. Earlier Excel versions do not support the function and may show a different error, such as #NAME?, rather than a spill error. Excel for Mac and Excel for the web also support dynamic arrays in current versions, though interface details can differ.
Find what is blocking the spill range
Start by selecting the cell showing #SPILL! and looking at the dotted outline. Any existing content inside the intended result area can prevent the array from appearing. A cell that looks empty may still contain a space, an apostrophe, or a formula returning an empty string, so inspect the cells rather than relying only on their appearance.
Depending on the cause, Excel may show an error explanation or offer an option to select obstructing cells. You can also inspect the outlined range yourself. If the blocking data matters, do not delete it just to remove the error: move the FILTER formula to a clear area, or move the existing data elsewhere. Once the range is available, Excel will generally recalculate and display the results.
- Check every cell in the dotted outline, not just the first row.
- Look for spaces, formulas, or other values that appear blank.
- Check whether any cells in the range are merged.
- Confirm that the formula is not inside an Excel table.
- Make sure the result would not run past the worksheet’s last row or column.
Common causes and what to do
A blocked spill range is the most common cause. If even one cell Excel needs is occupied, the full array cannot spill. Clear the obstructing content only if it is safe to do so; otherwise, relocate the formula or the blocking data. Formatting alone, such as a fill color or border, does not usually block a spill, but actual cell content does.
Merged cells can also interfere. If a merged cell overlaps the intended output area, unmerge it or choose a different location for the formula. Merging is a layout feature, and dynamic arrays need ordinary worksheet cells in which to place each returned value.
Another cause is placing a formula that returns multiple results inside an Excel table. Dynamic array results cannot spill within the table body. Put the FILTER formula in a regular worksheet range outside the table instead. A large result may also be unable to fit near the bottom or right edge of the worksheet; move the formula farther from those boundaries or narrow the source data.
Less commonly, Excel cannot determine a spill range because the formula depends on a changing or uncertain array size. The error explanation may identify this as an unknown-size spill. Review the formula’s references and, where appropriate, use a fixed source range or a calculation that returns a predictable array.
Fix it step by step
- Select the
#SPILL!cell and note the dotted outline showing the intended output area. - Inspect the outlined cells for values, formulas, spaces, or merged cells. Check the error message for a more specific cause.
- Decide whether to clear the obstruction, move the existing content, or relocate the FILTER formula. Preserve any data you still need.
- If the formula is in a table, place it in an ordinary worksheet cell outside the table.
- Review the formula’s input and criteria ranges. They should have compatible dimensions, and the criteria should express the condition you intend.
- Allow Excel to recalculate and confirm that the returned rows and columns are the ones you expected.
If you clear a cell and the error remains, inspect the full outlined area again. There may be more than one obstruction, or the formula may need more room than the visible first row suggests. If the error message names a merged cell, table, or worksheet boundary, address that cause directly rather than repeatedly changing the criteria.
A FILTER example and its expected result
Suppose A2:C6 contains five records, with order numbers in column A, regions in column B, and amounts in column C. The records are 101 / West / 120, 102 / East / 90, 103 / West / 75, 104 / North / 60, and 105 / West / 200. Cell E2 contains the text West. Enter the formula below in G2, with the three-column output area clear:
=FILTER(A2:C6,B2:B6=E2,"No matching rows")
The formula returns three rows: 101 / West / 120, 103 / West / 75, and 105 / West / 200. The results occupy G2:I4. If any of those nine cells is occupied or overlaps a merged cell, Excel may show #SPILL!. Moving the formula farther from existing data can be a safer fix than deleting cells.
The source range and criteria range in this example each cover five rows. Keeping them aligned matters: FILTER needs the include array to correspond to the rows or columns being filtered. If you accidentally make one range longer than the other, Excel may return a calculation error rather than the intended filtered list. That is a separate issue from a blocked spill range.
Distinguish #SPILL! from no matching results
A FILTER formula with no matching records does not necessarily have a spill problem. If the optional third argument, [if_empty], is omitted and no rows match, FILTER returns #CALC!. In the example above, the third argument supplies the message "No matching rows", so a missing match produces that text instead of #CALC!.
That text is a result, not an error, and it occupies the formula’s output cell. If you are troubleshooting, check the exact error shown in the anchor cell: #SPILL! points to where results should go, while #CALC! may indicate that no matching array was returned. Changing the condition to a value that exists can help confirm whether the filter logic is working.
Tables, references, and version differences
Tables remain useful for storing and sorting structured data, but place a multi-cell FILTER result outside the table. For example, if a table is named SalesTable and has a Region column, you can use its structured references from a regular cell elsewhere on the sheet. With the formula entered outside the table, the result can spill into ordinary worksheet cells, provided that range is clear.
Keep the output area separate from other data that may grow. A formula that works today can show #SPILL! later if additional matches extend its results into cells someone has filled in. Leaving an open area for the output, or placing results on a dedicated worksheet, makes that less likely.
In Microsoft 365, FILTER recalculates as source data changes, and its output may expand or shrink. Excel 2021 also supports FILTER, but some newer functions available in Microsoft 365 may not be available in that release. If you share the workbook with someone using an older version, they may not see the same dynamic-array results. Excel for Mac and Excel for the web use the same basic rule: spill output needs room, and a table is not a spill area.
Frequently Asked Questions
Why does FILTER say the spill range isn’t blank?
At least one cell Excel needs for the results contains something, even if it appears empty. Inspect the dotted outline for values, spaces, or formulas, then clear or move the obstruction if appropriate.
Can I use FILTER inside an Excel table?
You can use FILTER to return table data, but a multi-cell dynamic array cannot spill within a table’s body. Enter the formula in a regular worksheet range outside the table and leave enough room for the results.
Why does FILTER return #CALC! instead of #SPILL!?
If there are no matches and the formula does not specify its optional empty-result argument, FILTER can return #CALC!. Add an alternative result, such as "No matches", or check whether the criteria match any source rows.
Will clearing the cells fix every FILTER error?
No. Clearing an occupied spill range can fix #SPILL!, but it will not correct mismatched ranges, an invalid formula, or a no-match result. Read the exact error and check the formula as well as the destination cells.
Does FILTER work in older versions of Excel?
FILTER is supported in Microsoft 365 and Excel 2021 or later. Excel 2019 and earlier do not include the function, so an older installation may show an unsupported-function error instead of producing a dynamic array.
Related
- Excel Data Validation Simple List: Create and Manage It
- Excel Data Validation List: Can It Show Two Columns?
- Excel Data Validation List With a Blank Option
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Error Loading Add-Ins in Excel: Causes, Checks, and FixesIf Excel says “Error loading add-ins,” first identify the add-in type, then disable it in Excel and restart:…
- Can’t Hyperlink in Excel? Create and Fix LinksYes, you can add clickable hyperlinks in Excel to websites, files, email addresses, and places in a workbook.…
- Excel Filter Not Working? Fix the Most Common CausesIf an Excel filter is missing rows, showing unexpected results, or not responding, first check that the filter…
- Excel Dropdown List Error? Causes and FixesAn Excel dropdown list error usually comes from a missing or incorrect Data Validation source, a disabled in-cell…
- Excel Cell Errors: What They Mean and How to Fix ThemAn error in an Excel cell means Excel could not calculate or return the result a formula requests;…
- Excel File Extension Repair Online: Fix Corrupt XLSXYes, you can repair a corrupt Excel file extension online using dedicated file repair websites, but the results…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.