Excel FILTER Spill Error: Causes and Fixes

A #SPILL! error in an Excel FILTER formula means Excel found results to return but cannot place the full result in the worksheet. Select the formula cell, clear or move whatever is blocking its intended output area, and the results should spill into the now-empty cells.

What FILTER Does and Why Results Spill

FILTER is a dynamic array function in Microsoft 365 and Excel 2021 or later. Instead of returning just one value, it can return multiple matching rows or columns. Excel puts the formula in one cell, then displays the rest of the results in adjacent cells automatically. This output area is called the spill range.

For example, with records in A2:C100, where column A contains dates, column B contains regions, and column C contains order statuses, this formula returns every row whose status is Open:

With dates in A2:A100, regions in B2:B100, and statuses in C2:C100, enter this formula in E2:

=FILTER(A2:C100,C2:C100="Open","No matches")

The formula sits in E2, but its matching rows need room below and to the right. If it finds eight records, Excel needs a clear area of three columns by eight rows, starting at E2. You do not copy the formula down: the spill range is generated from the single formula in E2.

FILTER is available in Microsoft 365 and Excel 2021 or later. In Excel for Mac and Excel for the web, the function also returns a dynamic array in supported versions. The basic diagnosis is the same, although the position and wording of error menus can differ slightly between platforms.

How to Identify the Actual Blocker

Click the cell containing #SPILL!. Excel usually outlines the area where the results are meant to appear with a dashed border. Inspect every cell inside that outline, not only the first cell next to the formula. A blocker can be several rows or columns away from the formula.

In desktop Excel, select the error cell and open the warning icon that appears beside it. If available, choose Select Obstructing Cells to jump to a cell preventing the spill. This is especially helpful when the output is large or a blocking cell contains a formula that looks blank.

Common blockers include:

A value, text, number, or formula in any cell of the intended spill range.
A space or other invisible character left in a cell.
A formula that displays an empty string. It may look blank, but the cell still contains a formula.
Merged cells within the output area.
An attempt to spill results from inside an Excel Table.
An output range that would extend past the bottom or right edge of the worksheet.

Cell formatting by itself, such as a fill color or border, does not normally block a spill. Focus first on contents, merged cells, and the formula’s location rather than removing useful formatting.

Clear or Move Cells Blocking the Spill

If the outlined range contains data you need, do not simply delete it. Move that data to another location or move the FILTER formula so its results can expand into an empty area. If the contents are unwanted, select the blocking cells and use Home > Clear > Clear Contents. This removes values and formulas while preserving the cells’ formatting.

Pressing Delete after selecting a cell also removes its contents in many Excel setups, but check that the selection does not include information you intend to keep. If a cell appears empty, select it and look at the formula bar. A formula such as one that returns an empty string still occupies the cell and can obstruct the spill.

For merged cells, select the merged area and use Home > Merge & Center to turn off the merge, then make sure the output cells are empty. A dynamic array cannot spill through merged cells. Unmerging may change the appearance of a report, so consider moving the formula or adjusting the layout instead.

After clearing or moving the obstruction, Excel normally recalculates automatically. If it does not, check Formulas > Calculation Options and confirm that Automatic is selected. Manual calculation can leave displayed results out of date, though it is not itself the usual cause of a #SPILL! obstruction.

When a Formula Is Inside an Excel Table

Excel Tables are useful for structured data, filters, and automatically expanding source ranges, but spilled array formulas cannot spill from within a table. If you enter FILTER in a table cell and expect multiple results to fill neighboring table rows, Excel can return #SPILL! because the table’s calculated-column structure does not provide a normal spill area.

A practical solution is to put the FILTER formula in a worksheet cell outside the table, leaving enough empty space for the result. The source can remain a table. For example, if the table is named Orders and has a Status column, a formula outside the table can refer to its structured columns.

With an Excel Table named Orders containing a Status column and with the formula entered outside the table in E2, use:

=FILTER(Orders,Orders[Status]="Open","No matches")

This returns the matching table rows into worksheet cells, provided the spill area is clear. If the table grows, structured references can include its new rows automatically. If you specifically need a formula inside a table, use a formula that returns one result per row rather than a multi-cell array, or place the dynamic-array report beside the table.

Check the FILTER Ranges and Match Logic

Not every FILTER problem is a spill error. The array being filtered and the include condition must have compatible dimensions. If the source contains 99 rows, the include range should normally contain the corresponding 99 TRUE-or-FALSE tests. Mismatched ranges can produce a calculation error rather than a blocked spill.

With regions in B2:B100 and statuses in C2:C100, this example returns rows from A2:C100 where the region is West and the status is Open:

With records in A2:C100, region values in B2:B100, status values in C2:C100, and the formula in E2, use:

=FILTER(A2:C100,(B2:B100="West")*(C2:C100="Open"),"No matches")

Multiplying the two conditions means both must be true. The formula can return several rows, so it still needs an unobstructed spill area. Adding an if_empty value such as “No matches” handles the case where no rows meet the conditions; without that optional argument, FILTER can return #CALC! when there are no matches. That is a different error from #SPILL!.

Also check whether the formula’s results are larger than expected. A condition that matches every record may require far more output cells than a condition that matches only a few. Whole-column references can create unnecessary work and make the output size difficult to anticipate; use a realistic range or a table reference when appropriate.

Understand Other Spill-Related Behavior

Only the top-left cell of a spilled array contains the formula. The other cells show results generated by that formula, sometimes called spill cells. You can edit the formula in its anchor cell, but you cannot independently edit one of the generated results. Trying to type into a spill cell will obstruct the array and can cause #SPILL! in the anchor cell.

You can refer to the whole spilled result with the spill-range operator #. For example, if a FILTER formula is in E2, a later formula can use E2# to refer to its current output. This reference expands or contracts as the FILTER results change. It does not fix a blocked spill, and it will not help if the original formula cannot produce its results.

Be careful when inserting rows, adding notes, or placing totals beside a dynamic report: those cells may eventually overlap the spill area as the number of matches grows. A report that works today can develop #SPILL! when new source records cause more results. Leave a buffer or put dependent calculations outside the area that may expand.

A Reliable Troubleshooting Sequence

Use this order to find the cause without changing the source data unnecessarily:

Select the #SPILL! formula cell and note the dashed outline showing the intended output area.
Use the warning icon’s obstruction option if Excel offers it, or inspect the outlined cells manually.
Check suspicious cells in the formula bar, including cells that look blank. Move needed values; clear only unwanted contents.
Look for merged cells and check whether the formula was entered inside an Excel Table.
Confirm the result can fit on the worksheet and that the include and source ranges have matching dimensions.
Recalculate or edit the formula only after you have addressed the actual cause. If there are no matching records, supply FILTER’s optional if_empty argument rather than treating that case as a spill obstruction.

Changing the formula to IFERROR is usually not a real repair. It can hide an error message, but it does not make room for the intended results. Diagnose the spill range first; use error-handling logic only when you deliberately want a different display for a genuine no-match or calculation case.

Prevent Future #SPILL! Errors

Reserve an open area for dynamic-array reports and avoid placing manual notes, totals, or separate formulas where the results may grow. If the report is near the bottom of a sheet, move it upward or to a new worksheet. If its source changes regularly, leave more room than the current number of matches requires.

Keep the source data in a Table if that makes it easier to manage, but place the FILTER output outside the Table. Avoid merged cells in report areas that need to expand. For shared workbooks, tell other editors which cells contain spill formulas and which surrounding cells should remain clear.

These practices apply in Excel for Microsoft 365 on Windows and are broadly the same in current Mac and web versions. Mac users may see different ribbon placement or context-menu wording, but the key check remains whether the complete output range is available and unmerged.

Frequently Asked Questions

Why does FILTER show #SPILL! when the cells look empty?
One or more cells may contain a space, an invisible character, or a formula returning an empty string. Select the cells inside the dashed spill outline and inspect the formula bar; clear or move contents only after confirming they are not needed.

Can formatting cause a FILTER spill error?
Ordinary cell formatting does not usually block a spill. Cell contents, merged cells, table placement, or a spill range that cannot fit are more likely causes. Clear contents rather than formatting when removing an obstruction.

Why does FILTER return #CALC! instead of #SPILL!?
FILTER returns #CALC! by default when no records match and no if_empty value is supplied. Add a suitable third argument, such as “No matches”, if that is the result you want to display. #SPILL! instead means Excel cannot place the returned array in the worksheet.

Can I use FILTER inside an Excel Table?
A FILTER formula that needs to spill multiple cells cannot spill from within a Table. Put the formula in a normal worksheet range outside the Table and leave room for its results; the formula can still use the Table as its source.

Do I need to copy the FILTER formula down?
No. Enter it once in the top-left cell of the intended output. Excel fills the spill range automatically. Copying it down can create overlapping arrays or additional obstructions rather than extending the original result.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.