Modern versions of Microsoft Excel introduce dynamic array formulas, allowing calculation functions such as FILTER, UNIQUE, SORT, SEQUENCE, and XLOOKUP to output multiple values across adjacent cells automatically. However, when the designated output zone contains existing data, merged cells, or boundaries that obstruct formula expansion, Excel interrupts execution and displays the #SPILL! error.
What Causes the #SPILL! Error in Excel?
Unlike traditional formulas constrained to a single cell, dynamic array formulas calculate their entire output matrix and attempt to populate neighbouring cells. Excel throws a #SPILL! warning due to several distinct conditions:
- Spill Range Is Not Blank: The most frequent cause; one or more cells in the required calculation range already contain text, numbers, formulas, or invisible characters such as spaces.
- Merged Cells: Dynamic arrays cannot populate or split merged cells situated anywhere within the intended output range.
- Indeterminate Range (Volatile Sizes): Formulas that reference entire columns (such as
A:A) inside functions likeSEQUENCEcreate an array with over one million rows, exceeding worksheet grid dimensions. - Excel Tables Incompatibility: Dynamic arrays that spill into multiple cells are currently unsupported inside formal Excel Tables (created via Ctrl + T).
- External Workbook Links Blocked: References to closed external workbooks that cannot dynamically report array bounds during formula evaluation.
Method 1: Identify and Clear the Obstructing Cells
Excel provides an intuitive visual guide to help you discover exactly which cells are blocking the dynamic array expansion.
- Click on the cell displaying the
#SPILL!formula. - Notice the thin dashed border extending across adjacent empty rows and columns; this indicates the expected spill boundary.
- Click the warning error icon (exclamation mark inside a diamond) next to the cell.
- Select Select Obstructing Cells from the contextual menu.
- Excel will automatically highlight the precise cells containing conflicting values or invisible space characters.
- Press the Delete key on your keyboard to clear the obstructing contents. The formula will instantly recalculate and display values across the cleared range.
Method 2: Unmerge Cells in the Output Range
Merged cells break the underlying worksheet grid structure, making dynamic allocation impossible.
- Select the cells located beneath and to the right of your dynamic array formula.
- Navigate to the Home tab on the Excel ribbon.
- In the Alignment group, look at the Merge & Center button. If it is highlighted, merged cells exist in your selection.
- Click the dropdown arrow next to Merge & Center and choose Unmerge Cells.
- Return to the primary formula cell, press F2 to enter edit mode, and press Enter to trigger dynamic array re-evaluation.
Method 3: Restrict Whole-Column Formula References
Referencing entire columns forces functions to evaluate all 1,048,576 rows of a modern Excel worksheet, spilling beyond the grid capacity.
- Review the dynamic formula displaying the spill error. For example, check if it reads:
=UNIQUE(A:A) - Replace broad column references with a bounded, precise range:
=UNIQUE(A2:A500) - Alternatively, use structured references or wrap the array in dynamic range operators:
=UNIQUE(FILTER(A2:A1000, A2:A1000<>"")) - Press Enter to evaluate the bounded data set.
Method 4: Convert Excel Tables to Standard Ranges
If you authored dynamic array formulas inside a structured Excel Table, Excel prevents spilling to preserve table integrity.
- Click anywhere inside the Excel Table containing the formula.
- Click the Table Design tab that appears on the top ribbon.
- In the Tools section, click Convert to Range.
- Click Yes when prompted to confirm the conversion.
- Once converted to standard spreadsheet rows and columns, the dynamic array formula will immediately spill without restriction.