Handling the SPILL Error

When a dynamic array formula cannot lay out its full result, Excel shows #SPILL! in the anchor cell. The formula is usually correct; the problem is that something is in the way of where the results need to go.

Think of it like trying to park a bus in a space already holding a car. The bus is fine, but the spot is blocked.

The most common cause is that one or more cells in the spill range already contain data. If =UNIQUE(A2:A12) needs to fill C1:C3 but C2 holds a stray value, the spill is blocked.

The fix is simple: select the anchor cell, look at the dashed spill outline, and clear any cells inside it that are not empty.

Click the cell showing #SPILL!. Excel draws a dashed border around the area the result wants to occupy. Any non-empty cell inside that border is the culprit.

You can also click the warning triangle next to the cell and choose Select Obstructing Cells, which jumps straight to the offending cells so you can delete their contents.

A spill range cannot overlap any merged cells. Even an empty merged cell counts as an obstruction because the spill needs separate, individual cells to fill.

If your output lands on a merged title bar or label, unmerge those cells (Home tab, Merge and Center toggle) and the formula will spill correctly.

A spill also fails if it would run off the edge of the worksheet or into a table. For example a formula near the bottom row that needs to spill downward has nowhere to go.

Move the formula higher or to a column with space below it. Make sure there are as many empty cells as the result needs in the spill direction.

Excel Tables (created with Ctrl+T) do not allow dynamic arrays to spill inside them, because a table expects each cell to be editable.

If your spilling formula sits in a table column, you will get #SPILL!. Either convert the table back to a normal range (Table Design, Convert to Range) or move the formula outside the table.

Pointing a spilling formula at an entire column can also trigger #SPILL! because the result becomes enormous and may collide with other content or exceed limits.

For example =A:A*2 tries to spill a million rows. Restrict the reference to the actual data, like =A2:A100*2, so the spill has a manageable, well-defined size.

Hover over the warning triangle on a #SPILL! cell and Excel tells you exactly why. Common messages include Spill range isn't blank, Spill range has merged cells, and Spill range is too big.

Always read this tooltip first. It points you straight to the cause instead of guessing, and each message maps to one of the fixes you just learned.

It is important to remember that a #SPILL! error rarely means your formula logic is wrong. The calculation succeeded; only the placement failed.

Once you clear the obstruction, the same untouched formula spills perfectly. Compare this to #VALUE! or #REF!, which usually do signal a real problem inside the formula itself.

You type =SORT(UNIQUE(A2:A20)) in D1 and get #SPILL!. You select D1, see the dashed outline cover D1:D5, and notice an old note sitting in D3.

You delete the contents of D3. Instantly the formula spills the sorted distinct list down D1:D5. No edit to the formula was needed at all.

A little planning avoids most spill errors. Give your dynamic array formulas an empty column or area to grow into, and keep labels and notes away from the spill direction.

When you reference another spill, use the # operator like =SUM(D1#) so your formulas adapt as ranges resize. Build outputs in clear, open space and #SPILL! will rarely appear.

Test your understanding of the SPILL error.

You learned that #SPILL! is a placement problem, not a logic bug:

• Most often a cell in the spill range is not blank.
• Merged cells and Tables block spilling.
• Avoid full-column references that make the result too big.
• Use Select Obstructing Cells and the tooltip to find the cause.

That completes the Dynamic Arrays and Spilling course. You can now return many results from one formula and fix spills with confidence.