[Excel] Error: #SPILL! — How to Fix It

Summary

The #SPILL! error appears when a dynamic array formula can’t return its results because the intended spill range is blocked, merged, or too small. Functions like FILTER(), SEQUENCE(), and UNIQUE() automatically “spill” multiple results, but if anything obstructs their output range, Excel shows #SPILL!. To fix it, clear or unmerge cells in the spill area, or resize your formula output. Available in Excel 2019+ and Microsoft 365.

Context

Dynamic arrays introduced in modern Excel allow formulas to return multiple results at once. When you enter a single-cell formula like =SEQUENCE(10), Excel expands (“spills”) results into adjacent cells. If the destination range is blocked by data, formatting, merged cells, or sheet boundaries, the array can’t expand, leading to #SPILL!. The small yellow warning icon beside the cell gives detailed diagnostic messages such as “Blocked by merged cell,” “Blocked by another value,” or “Spill range is too big.” Understanding and clearing these constraints restores normal function behavior.

Probable Cause

  • Blocked spill range. Existing values, formatting, or conditional formatting rules prevent the array from expanding.
  • Merged cells in spill zone. Dynamic arrays cannot output into merged cells — even partially merged ones.
  • Output range exceeds sheet boundaries. Large SEQUENCE() or RANDARRAY() results may exceed available cells.
  • Dynamic references inside tables or structured references. Tables do not support spilling formulas directly inside them.

Quick Fix

  1. Check the error message. Click the warning icon beside #SPILL! — Excel highlights the blocked area or provides a cause.
  2. If “Blocked by another value” → clear cells in the intended output range.
  3. If “Blocked by merged cell” → unmerge all cells in that region (Home → Merge & Center → Unmerge).
  4. If “Spill range is too big” → adjust formula dimensions or restrict output using INDEX().
  5. If formula resides inside a Table → move it outside or convert the table back to a range (Ctrl + T → Convert to Range).
# Example 1: Simple unblocked spill
=SEQUENCE(5)
# Expands into 5 rows automatically

# Example 2: Restrict output to one value
=INDEX(SEQUENCE(5), 1)

Full Example

Scenario 1: A FILTER() formula shows #SPILL! because existing data blocks the spill area.

=FILTER(A2:A20, B2:B20="Yes")
→ #SPILL!

Diagnosis: Cells below the formula contain text or formatting preventing the spill.

Fix: Select the formula cell → click the warning icon → “Select Obstructing Cells” → delete or move those values. The formula will automatically expand once the range is cleared.

Scenario 2: Formula within a Table.

=SEQUENCE(10)
→ #SPILL!

Fix: Convert the table to a normal range or move the formula outside the table, since structured tables don’t support spilling arrays.

Scenario 3: Output range too large.

=SEQUENCE(100000,100000)
→ #SPILL!

Fix: Reduce the dimensions or limit results with INDEX() or TAKE().

Pitfalls & Debug

  • Symptom → #SPILL! after copying data nearby. Fix → Clear the affected cells or move the formula upward.
  • Symptom → Array formula in table column. Fix → Move formula outside the table; tables don’t support dynamic spilling.
  • Symptom → Partial merge near target range. Fix → Unmerge all affected cells to restore spill behavior.
  • Symptom → Formula returns more data than visible rows. Fix → Reduce function dimensions or filter input data.
  • Symptom → Using indirect references like OFFSET() or INDIRECT() causing irregular spill behavior. Fix → Apply @ operator or restrict the formula with INDEX().

Validation & Next Steps

To verify the fix, select the cell and look for the blue spill border — this shows the intended output area.

Formulas → Error Checking
F2 (Edit mode) → confirm target range is clear

Test the same formula on a blank sheet. If it spills correctly there, the problem lies in blocked or merged cells on your original sheet. For consistent design, keep dynamic array formulas separate from manual data regions.

Sources

https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error

https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior

https://support.microsoft.com/en-us/office/filter-function

Labels

Tool/Excel, OS/Cross-platform, Topic/Dynamic Arrays & Spill Behavior