Excel #SPILL! error: the spill range isn't blank

Hi, it's BlueByte. If a formula like =UNIQUE(...) or =SORT(...) shows #SPILL! instead of results, the formula is correct — it just has nowhere to put its output. Let's read the exact reason Excel gives, find what is blocking the range, and clear it. The formula fills in on its own the moment the space is free.

Read the reason under the error first

Select the formula cell and look under the yellow warning icon. Excel names the exact blocker — most often "Spill range isn't blank", but sometimes "Spill range has merged cell", "Spill range in table", "Spill range too big", or "Spill range is unknown". That reason decides the fix, so read it before doing anything.

Why the formula can't place its result

A dynamic array formula returns more than one value and needs to spill into the cells below and to the right of where you typed it. If anything blocks that range, Excel refuses rather than overwrite your data. In order of how often it happens:

• The range is not empty — a value, a space, or a number formatted to look blank sits in a cell the result would fill.
• A merged cell falls inside the spill range.
• The formula is inside an Excel Table — tables do not support spilling at all.
• The result would run past the sheet's edge, or its size is not knowable because a volatile function is nested inside.

Clear what's in the way

For the common "isn't blank" case, let Excel take you to the blocker:

Warning icon → Select Obstructing Cells → Delete the highlighted cells

Excel jumps straight to the cell or cells in the way; delete or move them and the formula spills. For the other reasons: unmerge the spill range (Home → Merge & Center → Unmerge Cells), or move the formula out of the Table (cut it to a normal cell, or Table Design → Convert to Range).

A real case: a leftover total

You enter =SORT(UNIQUE(A2:A500)) in C2 and get #SPILL!. Selecting C2 shows a dashed border running down column C, and the reason reads "Spill range isn't blank". You click the warning icon, choose Select Obstructing Cells, and Excel highlights C40 — a total someone typed months ago. You delete C40, and the sorted unique list fills C2 downward with a thin blue border, confirming a clean spill. You did not re-enter the formula; clearing the range was enough.

Confirm it spilled cleanly

Select the top cell of the result. A thin blue border around the whole range means it spilled. If it still shows #SPILL!, read the reason again — you may have cleared one blocker while another remains.

Keep it from happening again

Leave empty space below and to the right of dynamic array formulas, and keep them out of Tables. When a blocking cell looks empty but still blocks, it almost always holds a space or a non-printing character — select it and press Delete to be sure.

How this differs from #REF! and #VALUE!

#SPILL! is only about room to spill — the formula works, it just cannot place its output. That is different from #REF! (an invalid cell reference, usually a deleted cell) or #VALUE! (a wrong argument type), which mean the formula itself is wrong. So when you see #SPILL!, look at the cells around the result, not at the formula.