The #SPILL! error in Microsoft Excel occurs when a Dynamic Array formula attempts to return multiple values across neighboring cells, but the destination spill range is blocked, merged, unallocated, or infinite. Introduced with Excel 365 and Excel 2021, dynamic arrays like FILTER, UNIQUE, SORT, and XLOOKUP must spill freely into adjacent rows or columns.
In this article
- Quick Diagnostic Table: Why #SPILL! Happens
- 7 Proven Fixes for Excel #SPILL! Errors
- 1. Clear Blocked Cells in the Spill Boundary
- 2. Eliminate Merged Cells
- 3. Dynamic Arrays Outside Excel Structured Tables
- 4. Avoid Entire-Column Array Calculations
- 5. The Spill Range Operator (#) in Downstream Formulas
- Continue learning
Quick Diagnostic Table: Why #SPILL! Happens
| Spill Error Cause | Why It Happens | Instant Solution |
|---|---|---|
| Spill Range Isn’t Blank | Existing text, space, or number in the destination area. | Clear or delete the blocking cells highlighted by the dashed outline. |
| Merged Cells in Range | Dynamic arrays cannot spill into or across merged cells. | Unmerge all cells inside the intended output area. |
| Inside an Excel Table | Structured Tables (ListObjects) do not support dynamic spilling. | Move formula outside table, or convert Table to standard Range. |
| Volatile or Infinite Size | Formula references whole columns (e.g., A:A) inside array logic. | Limit references to actual data bounds (e.g., A2:A1000). |
| Worksheet Grid Edge | Formula attempts to spill beyond row 1,048,576 or column XFD. | Reposition formula higher up or adjust array dimension bounds. |
7 Proven Fixes for Excel #SPILL! Errors
1. Clear Blocked Cells in the Spill Boundary
When you click on a cell displaying #SPILL!, Excel draws a dashed thin boundary showing the required rectangular zone. Click the error exclamation icon next to the cell and choose Select Obstructing Cells. Press Delete to remove blocking content.
2. Eliminate Merged Cells
Merged cells are the most frequent hidden culprit in corporate dashboard templates. Select the entire target column or region, go to the Home tab, and click Merge & Center to unmerge. Use Center Across Selection in Format Cells (Alignment tab) instead of merging.
3. Dynamic Arrays Outside Excel Structured Tables
Excel Tables require every column to have a single, rigid row structure. Spilling formulas like =SORT(UNIQUE(A2:A50)) placed directly within a structured table column will automatically return #SPILL!. Place the formula on a calculation sheet outside the table, and reference table headers using structured references: =SORT(UNIQUE(SalesTable[Region])).
4. Avoid Entire-Column Array Calculations
❌ WRONG (Returns #SPILL! or freezes Excel):
=FILTER(A:B, A:A="North")
✅ CORRECT (Fast, bounded calculation):
=FILTER(A2:B5000, A2:A5000="North")5. The Spill Range Operator (#) in Downstream Formulas
To refer to the entire output of an existing dynamic array, use the hash sign (#) after the top-left cell. For example, if cell D2 contains =UNIQUE(A2:A100), reference all unique results with =SORT(D2#) or =COUNTA(D2#). This prevents manual range adjustments as source data scales.
Test formula syntax online with our free Interactive Formula Lab or practice hands-on shortcuts in the Shortcut Trainer.
Stuck on a step? Ask a question and the AI answers using this article.