Excel Formula Spill Error: Causes, Checks, and Fixes

An Excel #SPILL! error means a formula is trying to return results into multiple cells, but Excel cannot place the full result in the worksheet. Select the error cell, inspect the outlined spill range, and remove or resolve whatever is preventing the results from appearing; the formula itself may be working correctly.

What the spill error means

In Microsoft 365, many formulas can return an array of values instead of just one value. Excel places the first result in the cell containing the formula and, when needed, fills neighboring cells with the remaining results. This behavior is called spilling, and the formula cell is often called the anchor cell.

For example, a formula that sorts a list may return the entire sorted list at once. If any cell where Excel needs to put that list is unavailable, Excel shows #SPILL! in the anchor cell rather than displaying an incomplete result. The error therefore often describes a problem with the destination cells, not a mistake in the formula’s calculation.

Spilled results are not separate formulas that you can edit individually. They are outputs controlled by the formula in the anchor cell. When the source data changes, Excel can recalculate the array and change how many cells it occupies.

How dynamic arrays work

Dynamic arrays are a modern Excel feature: a formula can return a result whose size changes to match its inputs. Functions such as SORT, FILTER, and UNIQUE can produce multiple rows or columns. In Microsoft 365, you enter the formula in one cell; Excel handles the returned array and adjusts the spill range as its size changes.

Suppose A2:A6 contains the names Mia, Leo, Ava, Noah, and Eli, and you enter a sorting formula in C2. The layout assumes the five names are in A2:A6 and the formula is in C2; the result spills into C2:C6 in alphabetical order: Ava, Eli, Leo, Mia, Noah. The formula is =SORT(A2:A6).

The cells occupied by the results are called the spill range. You can refer to the complete range by adding the spill operator, a hash mark, to the anchor cell reference. For example, if the sorted results begin in C2, a formula elsewhere can use =COUNTA(C2#) to count the current spilled results. With the five-name example, it returns 5. This reference expands or contracts with the spill range.

Find the obstruction before changing the formula

Start by selecting the cell displaying #SPILL!. Excel commonly displays a dashed outline around the area where the formula wants to return results and an error indicator with a more specific explanation. If the message identifies obstructing cells, use the offered option to select them, then inspect their contents before clearing anything.

Click the anchor cell containing the error and read the error message or tooltip.
Look inside the outlined spill range for values, formulas, merged cells, or a worksheet boundary that leaves too little room.
Check whether the formula is inside an Excel table or refers to a range that can grow unexpectedly.
Remove the obstruction, move the formula, or adjust the source or formula so the intended result fits.
Confirm that the complete result appears and that it still behaves correctly when the source data changes.

Do not clear a cell just because it looks blank. A formula that returns an empty string can make a cell appear empty while it still contains a formula, so it can block a spill. Check the formula bar or select the cell to see whether it has content. Formatting alone, such as a fill color or border, does not normally prevent a result from spilling.

Common cause: cells in the spill range are not empty

The most frequent cause is existing content in one or more destination cells. A number, text value, formula, or even a space entered into a cell can block the array. Excel will not overwrite it automatically, because doing so could destroy data. The error may result from just one occupied cell anywhere in the intended spill range.

For instance, imagine that a formula in D2 needs to return five rows, but D5 contains an old note. Excel cannot fill the entire range, so the anchor cell shows #SPILL!. Delete or move the note only if it is no longer needed; otherwise, move the formula to an open area or make room for its output. If the formula’s output size varies, leave extra space for likely future results as well.

Clearing contents is different from deleting cells. Deleting a cell can shift nearby worksheet data, while clearing a confirmed obstruction leaves the worksheet structure in place. Before removing anything, make sure it is not information another formula or person relies on.

Tables, merged cells, and worksheet edges

Excel tables are useful for structured data, filters, and automatically expanding columns, but dynamic array formulas cannot spill within a table itself. If a formula that returns several results is entered inside a table, place it in a normal worksheet range outside the table. You can still have the formula refer to table data; the key issue is where the spilled results need to appear.

Merged cells can also block an array when a merged cell overlaps the intended spill range. Unmerge the affected cells or place the formula somewhere the output does not cross merged cells. Before unmerging, check whether the merged layout is important; unmerging can change how the displayed content is arranged.

A spill also cannot extend beyond the worksheet’s row or column limits. A formula close to the bottom or right edge may have no room for all its results. Move it toward the top or left, or narrow the range it processes. Whole-column references can be especially problematic when a formula tries to return far more values than intended, so use a bounded source range or a table column when appropriate.

Other causes and formula checks

Not every #SPILL! error is caused by a visible value. Excel may report that the spill range is unknown when it cannot determine the array’s final size in advance. Some formulas involving changing or volatile calculations can produce this situation. If the error message points to an unknown range, review the formula’s construction and whether it depends on a result whose dimensions are not settled when Excel evaluates it.

Large arrays can also be impractical to return. A formula that refers to an entire row or column may request an enormous result, or a result may run past the worksheet boundary. Narrowing the input often fixes the issue and can make recalculation more efficient. Check for accidental references such as a whole column when you meant to use only the rows containing data.

Review the formula bar to make sure the formula is in the cell you expect and references the intended source range. A formula copied down from another row may point to a different range than intended. Also consider whether an output is genuinely meant to occupy multiple cells. If you only need one value, revise the formula to return that value rather than trying to suppress the error without understanding why an array is being produced.

Why editing a spill result can be confusing

Only the anchor cell contains the dynamic-array formula. The other cells display its results, so typing into one of those cells will not edit just that individual result; Excel may report that you cannot change part of an array. To change the calculation, edit the anchor formula. To change a source value, edit the original input cells instead.

If you need to keep a snapshot of the displayed results rather than a live array, copy the spill range and use Paste Special > Values to place fixed values elsewhere. Those pasted values will no longer update when the source data changes. If the results must remain connected to their inputs, keep the formula in the anchor cell and preserve enough clear space for the full spill.

What a spill error is not

#SPILL! is different from errors such as #VALUE! or #NAME?, which usually indicate a problem with a value, operation, or function name. It also does not necessarily mean that Excel lacks support for the formula. If you see #NAME? instead, check spelling and version availability; if you see #SPILL!, first investigate the intended output area and the dimensions of the returned array.

Another misconception is that every formula producing several results needs to be copied down. With a dynamic-array formula, one anchor formula can fill its spill range automatically. Copying it into cells that the first formula needs may create the obstruction rather than solve it. Older Excel versions may handle some array formulas differently, so a workbook’s behavior can depend on which Excel version opens it.

Frequently Asked Questions

How do I fix a #SPILL! error in Excel?
Select the error cell, read Excel’s explanation, and inspect the outlined spill range. Clear or move any unwanted contents, or relocate the formula to an open range. If the obstruction is a merged cell, table location, or worksheet edge, change the layout or formula rather than deleting unrelated data.

Why does Excel show #SPILL! when the cells look blank?
A cell may contain a formula returning an empty string, a space, or other content that is not obvious from its appearance. Select the suspected cell and check the formula bar. Formatting by itself generally does not block a spill, but hidden content can.

Can a dynamic-array formula spill inside an Excel table?
No. A dynamic-array formula cannot spill within the table area. Put the formula in a regular worksheet range outside the table, while keeping references to the table as needed.

Can I edit one cell in a spilled result?
No. The results are controlled by the anchor formula, so edit that formula or change its source data. If you need independent, non-updating values, copy the results and paste them as values in another location.

Does #SPILL! mean my formula is wrong?
Not necessarily. It often means Excel cannot place a valid array result because the output area is blocked or too small. Read the error’s specific explanation and inspect the formula if the destination range is clear.

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.