Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #SPILL! error
Excel onlyWhat #SPILL! means
#SPILL! occurs in Excel when a dynamic array formula cannot write its results into the spill range because one or more cells in that range are occupied — in this specific variant, because those cells are merged. Excel cannot write individual values into sub-cells of a merged group, so the spill is blocked entirely.
Common causes
- The output range contains merged cells from a previous formatting operation, and Excel cannot split values into merged sub-cells.
- Headers or summary rows above the formula cell were merged for aesthetic reasons and now block the array from spilling down.
- A merged cell exists anywhere in the spill range — even a merge two rows below the formula will block the entire spill.
- The formula was moved adjacent to a merged table header without realizing the header merge extends into the formula's output zone.
- A copy-paste of formatted content brought merged cells into the area where a dynamic array formula was already present.
Example fix
Broken
=UNIQUE(A2:A100)Fixed
=UNIQUE(A2:A100)The formula itself is correct. Fix the spill by unmerging cells in the output range: select the spill range, then Home > Merge & Center > Unmerge Cells.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Select the spill range (click the formula cell, then Ctrl+Shift+End to extend to the expected output size), then Home > Merge & Center > Unmerge Cells.
- 2To find merged cells in the spill area: Home > Find & Select > Go To Special > Merged Cells, limited to the relevant range.
- 3Move the formula to an entirely separate area of the sheet that has no merged cells in the output zone.
- 4Instead of merging cells for visual formatting, use 'Center Across Selection' (Format Cells > Alignment > Horizontal > Center Across Selection) — it looks merged but is not.
- 5If you need the merged header to stay, place the dynamic array formula in a column or section where it will not intersect the merged region.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every #SPILL! and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
How do I find exactly which merged cell is blocking the spill?
Click the formula cell. Excel shows a dashed border around the expected spill range and a tooltip identifying the obstruction. Use Home > Find & Select > Go To Special > Merged Cells within that range.
Will unmerging cells break my formatting?
Unmerging removes the merge but leaves the content in the upper-left cell of the former merge. You will need to reformat. Use 'Center Across Selection' as a non-merging alternative for headers.
Does #SPILL! due to merged cells appear in Google Sheets?
Google Sheets does not support merged cells blocking array expansion in the same way. Sheets auto-expands arrays but will show an 'Array result was not expanded' error if output cells are occupied by other data.