How to Fix the #SPILL! Error in Excel (Dynamic Array Spill Range Blocked)

Modern Excel dynamic array functions (FILTER, UNIQUE, SORT, SEQUENCE, XLOOKUP) return multiple cells that automatically “spill” into adjacent rows and columns. When something prevents this range from populating, Excel throws #SPILL!.

---

🔍 How to Diagnose and Fix the Spill Range

Step 1: Click the Formula Cell

When you click on a cell with a #SPILL! error, Excel draws a dashed blue border around the exact rectangular area it is trying to fill.

Step 2: Clear the Obstructing Cells

• Look inside the dashed box for any text, numbers, or even invisible space characters.
• Select the blocking cells and press Delete.
• The formula will instantly expand and populate normally.

---

The 4 Common #SPILL! Scenarios

1. Spill Range Blocked by Data: A single character or number in cell E14 blocks a 50-row FILTER from expanding. Solution: Delete the obstructing data.
2. Merged Cells: Excel cannot spill dynamic arrays into merged cells. Solution: Unmerge all cells in the destination range.
3. Used Inside an Excel Table (Ctrl+T): Tables have fixed column structures and cannot accommodate variable spill heights. Solution: Place the formula on a normal worksheet grid outside the table.
4. Entire Column References: Writing =UNIQUE(A:A) forces Excel to calculate 1,048,576 rows. Solution: Restrict the range to actual data =UNIQUE(A2:A500).