Entering a modern dynamic array formula in Microsoft Excel—such as UNIQUE, FILTER, SORT, SEQUENCE, or XLOOKUP—only for the cell to immediately display the error code: “#SPILL!” halts analytical reporting workflows. Introduced in Microsoft 365 and Excel 2021, dynamic array calculations calculate results in a single formula cell and automatically populate (“spill”) their output across adjacent blank rows and columns. When Excel evaluates the expected rectangular output dimension of your formula but encounters an obstruction in the intended path—such as existing text, invisible non-printing whitespace, merged cells, or an intersecting formatted Excel Table—Excel cannot allocate the spill range and throws the #SPILL! error. This guide explains why spill errors occur and provides step-by-step methods to resolve them.
What Causes the #SPILL! Error in Microsoft Excel?
Excel dynamic array formulas trigger a spill error under specific worksheet circumstances:
- Spill Range Obstruction: Existing alphanumeric data, numbers, or formula cells located within the boundary required by the formula.
- Invisible Whitespace or Spacebar Characters: Cells that appear empty but contain a space character (
" "), apostrophe, or empty string (=""). - Merged Cells Within the Output Boundary: Merged cells anywhere within the formula’s projected rectangular range (dynamic arrays require unmerged individual cells).
- Formulas Located Inside an Excel Table (ListObject): Dynamic array formulas are fundamentally unsupported inside structured Excel Tables (
Ctrl + T) because tables manage their own row expansion. - Indeterminate or Volatile Output Dimensions: Functions like
RANDARRAYreferencing non-fixed bounds or entire worksheet columns (e.g.,A:A) attempting to spill over a million rows.
Method 1: Identify and Clear Spill Range Obstructions
Excel provides an intuitive visual indicator showing the exact rectangular perimeter required by your dynamic array.
- Click on the cell displaying the #SPILL! error.
- Look closely at your worksheet: Excel draws a distinctive dashed blue border around the required destination range.
- Click the yellow warning diamond (Error Checking icon) that appears next to the formula cell.
- Select Select Obstructing Cells from the contextual menu. Excel immediately highlights the specific cell or cells preventing the spill.
- Inspect the obstructing cells. If the data is redundant, press Delete on your keyboard.
- The instant the obstructing cells are cleared, the dynamic array automatically populates across the cleared range.
Method 2: Unmerge Cells in the Projected Range
Dynamic arrays cannot expand across merged cells because merged ranges break standard matrix indexing.
- Highlight the rows and columns underneath and to the right of your formula cell.
- Go to the Home tab in 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 drop-down arrow next to Merge & Center and choose Unmerge Cells.
- Press F9 to recalculate the sheet. The formula will spill cleanly.
Method 3: Move Dynamic Formulas Outside Structured Excel Tables
Excel Tables do not support formulas that return arrays of varying heights.
- If your formula resides inside a table (where column headers have drop-down filter arrows and alternating row colors):
- Cut the dynamic formula (Ctrl + X).
- Paste it into a standard worksheet grid cell outside the table (Ctrl + V).
- Alternatively, if you do not need the table structure, click anywhere inside the table, go to the Table Design tab, and click Convert to Range. Once converted to a normal grid, dynamic arrays spill without hindrance.
Method 4: Avoid Entire Column References in Dynamic Formulas
Referencing entire columns forces Excel to attempt populating 1,048,576 rows, exceeding worksheet limits.
- Inspect your formula for references like
A:AorB:B. For example:=UNIQUE(A:A) - Because column A contains blank cells all the way to row 1,048,576, the formula attempts to spill past the bottom edge of the sheet.
- Modify the formula to reference only the actual used data range:
=UNIQUE(A2:A500) - Or use dynamic structured references:
=UNIQUE(FILTER(A2:A1000, A2:A1000<>""))
Excel #SPILL! Error Diagnostic Matrix
| Error Sub-Type | Underlying Cause | Resolution |
|---|---|---|
| Spill range isn’t blank | Text or space characters in output path | Click ‘Select Obstructing Cells’ and press Delete |
| Spill range has merged cells | Merged cell spans across destination range | Select range and click Home > Unmerge Cells |
| Spill range in table | Dynamic arrays used inside structured ListObject | Move formula to standard cells outside table |
| Spill range is too large | Whole column reference (e.g. A:A) | Constrain formula reference to specific range (A2:A500) |
Clearing range obstructions and specifying exact data bounds permanently eliminates the #SPILL! error in Microsoft Excel.