Excel FILTER Spill Error: Why It Happens and How to Fix It

Errors & fixes · Updated November 19, 2026

An Excel FILTER spill error means Excel cannot place the formula’s results in the cells where they need to appear. Clear the blocked output area, move the formula outside a table or merged cells, or otherwise make room for the returned array; the exact fix depends on the cause shown by Excel’s error message.

What the FILTER spill error means

In Microsoft 365 for Windows, FILTER is a dynamic array function: one formula can return several matching values, and Excel fills the neighboring cells automatically. That group of results is called a spill range. If even one cell needed for that range cannot be used, Excel displays #SPILL! in the formula cell instead of returning the results.

The formula itself may be valid. The problem is often the worksheet layout, not the data or the criteria. For example, a formula may be set to return 15 matching records across three columns, requiring a 15-row-by-3-column area. A value, formula, merged cell, or other obstruction in that area can prevent the full result from appearing.

FILTER is available in Microsoft 365 and Excel 2021 or later. Older Excel versions may not support it as a dynamic array function. Also distinguish #SPILL! from #CALC!: FILTER returns #CALC! when there are no matches and its optional empty-result argument is omitted. An empty-result argument can handle that case, but it cannot clear a spill obstruction.

How FILTER creates a spill range

The function’s syntax is =FILTER(array,include,[if_empty]). The array is the range or values to return, include is a same-height or same-width test that identifies which items to keep, and if_empty is an optional value to show when nothing meets the test.

For example, with records in A2:C100, region names in B2:B100, and the formula entered in E2, use =FILTER(A2:C100,B2:B100="West","No matches"). If 12 records have West in column B, Excel returns 12 rows and three columns, occupying E2:G13. The result expands or contracts as the matches change, so cells in the expected output area must remain available.

FILTER does not copy its results into independent formulas in every destination cell. The formula in E2 controls the whole result. You can select the spill range by selecting the formula cell and using the displayed spill-range outline, but you cannot edit an individual result cell while the array formula controls it. Edit the source data, the formula, or the obstruction instead.

Find the specific cause in Excel

Select the cell showing #SPILL!. Excel usually displays an error icon and a short explanation, such as a blocked spill range or a spill range in a table. The wording helps identify which remedy applies. In many cases, the warning menu also offers Select Obstructing Cells, which can take you to the blocking cells.

If the explanation is not obvious, inspect the destination cells the formula would need. Look at the outline Excel shows for the intended spill range, and check cells inside it, including cells that appear blank. A cell containing a space, an invisible character, or a formula that returns an empty string is not necessarily empty for spill purposes. A format color may also make a value hard to see.

  • Check for typed values, spaces, or formulas anywhere in the expected output area.
  • Look for merged cells, Excel table boundaries, and formulas that already occupy part of the area.
  • Check that the formula is not trying to return results beyond the worksheet edge.
  • Read the error detail before changing the formula; different causes need different fixes.

Fix a blocked spill range

The most common cause is one or more cells in the output area that already contain something. Clear only the cells that should be available for the result, or move the FILTER formula to an open area. On Windows, select the obstructing cells and use Home > Clear > Clear Contents, or press Delete. Clearing contents preserves cell formatting; deleting cells and shifting neighboring data can have unintended effects, so do not use it unless that is what you want.

Before clearing a cell, confirm it does not contain information you need. If the obstruction is a formula, consider moving the FILTER formula or its output rather than removing that formula. If the blocked area overlaps another report, place the result on a separate worksheet or reserve a dedicated space for it.

When the result could grow over time, leave more room than the current match count suggests. A formula returning five rows today might return 50 after new records are added. Keeping a clear output zone reduces the chance that a future change creates a new #SPILL! error.

Fix table and merged-cell conflicts

Dynamic array formulas cannot spill inside an Excel table. If the FILTER formula is entered in a table column, Excel cannot expand the result through the table’s rows. Put the formula in a normal worksheet cell outside the table, with enough clear cells below and to the right, or use a different design if each table row must calculate its own value.

A table can still be the source of the filtered data. The limitation concerns the location where the spilled result must appear, not the fact that the input data is formatted as a table. If the formula returns an expanding list for a dashboard, place it in a regular range beside or on another sheet rather than inside the source table.

Merged cells can also obstruct a spill range, including when the merged area is not where the formula was entered. Unmerge the cells that overlap the intended output area, or move the formula to an unmerged region. On Windows, use Home > Merge & Center > Unmerge Cells. This changes the layout, so check where the existing cell contents remain before proceeding.

Check the output size and formula setup

A FILTER result may be larger than expected because the criteria match more records than anticipated or because the returned array includes several columns. Estimate both dimensions: how many rows can match, and how many columns does the array return? A formula filtering A2:C100 can return up to 99 rows and three columns. An obstruction near the bottom or right edge can be easy to overlook.

If the spill would extend beyond the worksheet boundary, Excel cannot display it. Move the formula to a location with enough rows and columns, or return a smaller range if that fits the task. Avoid fixing a boundary problem by arbitrarily limiting the results unless excluding later records is acceptable.

Also confirm the include range aligns with the array. If the records are in A2:C100, the region test should normally cover the same rows, such as B2:B100. A mismatched include range can cause a different formula error rather than a spill error, but checking the ranges helps distinguish a formula construction problem from a blocked destination.

Distinguish #SPILL! from other FILTER errors

Not every unexpected FILTER result is a spill problem. If the formula displays #CALC! because there are no matches, supply an appropriate third argument. With records in A2:C100 and regions in B2:B100, =FILTER(A2:C100,B2:B100="North","No matching records") returns that message when no row is North. This handles an empty result; it does not fix a cell blocking a nonempty result.

If you see #VALUE! or another error, inspect the source ranges and the criteria expression. For example, an invalid or incompatible range setup may be a formula issue rather than a destination issue. A result that appears shorter than expected may simply mean fewer records meet the condition. Check the actual data and criteria before assuming Excel has failed to spill.

Similarly, a formula showing a single value may be expected if only one record matches. Dynamic array formulas can return one cell or many. A visible spill border and the results themselves help establish whether the output has expanded successfully.

Prevent recurring spill errors

For reports that update regularly, separate the input data from the output area. Keep source records in a table if useful, then put the FILTER formula in a clear region outside that table. Avoid placing notes, totals, or manual calculations immediately next to a spill formula where the result could later expand into them.

  • Reserve a dedicated area for dynamic array results and keep it free of manual entries.
  • Use a separate worksheet for a result that can grow substantially.
  • Choose an output location that has room for the likely maximum number of records.
  • When copying a worksheet or formula, check whether another spill result now occupies the destination.
  • Use the error’s specific explanation rather than repeatedly editing the FILTER criteria.

These practices are especially useful when a workbook is shared. Someone may type a note into a cell that previously appeared blank because the current result was smaller. If new records later cause the result to expand, that note can become the obstruction. A clearly labeled output area makes it easier for collaborators to know which cells should remain untouched.

Frequently Asked Questions

Does adding an if_empty value fix #SPILL!?
No. The third FILTER argument handles a result with no matches, usually preventing #CALC! in that situation. #SPILL! means Excel cannot place a result that it needs to return. Remove or avoid the obstruction, or move the formula.

Why does FILTER work on one sheet but not another?
The output area may differ. One sheet might have clear cells while the other contains data, a merged range, a table, or another spill result. Select the error cell and check Excel’s explanation and the intended output area.

Can FILTER spill inside an Excel table?
No. The formula’s expanding result cannot spill within a table. Enter the formula in an ordinary worksheet range outside the table, leaving enough clear cells for its results.

Why is a seemingly blank cell blocking the result?
It may contain a space, another invisible character, or a formula returning an empty string. Select the cell and inspect the formula bar, or use Excel’s error prompt to identify the obstructing cell before clearing anything.

Is #SPILL! available in older Excel versions?
FILTER and dynamic arrays are supported in Microsoft 365 and Excel 2021 or later. Earlier versions may not support FILTER in the same way, so a spill-error fix may not be enough; use a compatible Excel version or another method to produce the filtered list.

Related

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.