How to Fix Excel ‘Spill’ Error (#SPILL!)

When entering modern dynamic array formulas in Microsoft Excel – such as =UNIQUE(), =FILTER(), =SORT(), or multi-cell =XLOOKUP() – the destination cell can return a jarring formula calculation alert: #SPILL!. Clicking the error indicator displays the diagnostic prompt: Spill range is not blank. This error occurs because dynamic array formulas automatically populate multiple adjacent cells (known as the ‘spill range’); if any obstacle blocks that expansion pathway, Excel aborts the calculation to prevent overwriting existing data.

Eliminating the #SPILL! error requires identifying obstructed cells in the array path, converting Excel tables to standard ranges, and preventing formula merging conflicts.

Why Does Microsoft Excel Return the #SPILL! Error?

Unlike legacy Excel array formulas entered with Ctrl + Shift + Enter, dynamic array formulas dynamically adjust their output boundaries. The calculation fails when:

  • Spill Range Is Obstructed: One or more cells in the required output range contain text, numbers, formatting spaces, or hidden zero-length strings (="").
  • Formula Used Inside an Official Excel Table: Microsoft Excel does not support dynamic array spill behaviors inside structured Excel Tables (created via Ctrl + T).
  • Merged Cells in the Expansion Path: Merged cells disrupt the geometric uniform grid required for multi-cell array allocations.
  • Unbounded Array Calculations (Entire Column References): Referencing an entire column (e.g. =UNIQUE(A:A)) commands Excel to attempt spilling 1,048,576 rows down to the bottom boundary.
  • Out of Memory / Volatile Array References: Complex recursive formulas generating indeterminate output lengths.

Step 1: Select and Clear the Obstructed Spill Range

Excel provides a built-in visual locator to pinpoint the exact cell blocking the formula:

  1. Click on the cell displaying the #SPILL! error.
  2. Notice the thin dashed border line that appears outlining the intended spill range on your worksheet.
  3. Click the yellow warning diamond icon next to the cell.
  4. Select Select Obstructing Cell from the context menu.
  5. Excel will immediately move your cursor focus to the exact cell blocking the formula.
  6. Press the Delete key on your keyboard to clear the obstructing contents.
  7. As soon as the cell is empty, the dynamic formula will calculate and populate the entire range automatically.

Step 2: Unmerge Cells in the Output Area

Merged cells break array dimensions and immediately trigger spill errors:

  1. Highlight the region of cells beneath and to the right of your formula.
  2. Switch to the Home tab in the Excel ribbon.
  3. In the Alignment group, click the Merge & Center dropdown button.
  4. Select Unmerge Cells.
  5. Click your formula cell and press Enter to recalculate the array.

Step 3: Convert Structured Tables to Standard Ranges

Dynamic array formulas cannot spill within the body of an Excel Table:

  1. Check if your formula is entered inside a table with banded rows and column header filter arrows.
  2. If your workflow requires dynamic arrays, convert the table to a normal range:
    1. Click anywhere inside the Excel Table.
    2. Switch to the Table Design tab in the ribbon.
    3. In the Tools group, click Convert to Range.
    4. Confirm with Yes when asked: Do you want to convert the table to a normal range?
  3. Alternatively, place the dynamic array formula in an empty worksheet area outside the table boundary and reference the table columns from there (e.g. =SORT(Table1[Product])).

Step 4: Avoid Entire Column References in Dynamic Formulas

Referencing full columns forces the array engine to generate millions of blank cells:

  1. Inspect your formula syntax. If it reads:
    =FILTER(B:B, A:A="Active")
  2. Change the full column reference to an explicit used range:
    =FILTER(B2:B500, A2:A500="Active")
  3. By bounding the input coordinates, the resulting spill range shrinks to match the actual data count, avoiding sheet boundary collisions.

Step 5: Use the Implicit Intersection Operator (@) for Single Values

If you only intended to evaluate a single value on the current row rather than spill an entire array:

  1. Prefix the range or function argument with the @ operator:
    =@VLOOKUP(A2, D:E, 2, FALSE)
  2. The @ operator forces implicit intersection, instructing Excel to return only the single scalar value corresponding to the current row, preventing multi-cell spill attempts entirely.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.