Excel #SPILL! Error Causes and Fixes

To start troubleshooting, select the formula cell. Excel will indicate the intended spill range. If the error menu offers Select Obstructing Cells, use it to locate any cells preventing the formula from expanding. Check those cells before clearing them, especially if other parts of a financial model depend on them.

Microsoft documents these error conditions and remedies in its guide to correcting #SPILL! errors.

Common Excel spill error causes

An overlapping dynamic array is one form of “spill range isn’t blank.” The existing array’s anchor or output occupies part of the new formula’s destination.

Excel version requirement

These examples require a dynamic-array-capable release, such as Excel for Microsoft 365, Excel 2021, Excel 2024, or a current version of Excel for the web. FILTER, SORT, UNIQUE, SEQUENCE, and spill references using # aren’t available with the same behavior in Excel 2019 and earlier.

Microsoft lists supported releases on the individual FILTER, SORT, UNIQUE, and SEQUENCE reference pages.

Reproducible worksheet setup

Create a worksheet named SpillDemo and enter this sample data:

Amount_USD and Threshold_USD are numeric USD values. Reserve E2:F10 for formula output and K1:K4 for controls.

Run each baseline formula separately from E2. Before each test:

1. Delete the previous anchor formula.
2. Clear any direct entries from E2:F10.
3. Confirm that E2:F10 is unmerged.

The formulas use commas as argument separators. Some regional settings require semicolons.

The expected results below are derived from the stated inputs. They’re reproducible checks, not claims that the tests have been run across every Excel version or platform.

Confirm that dynamic arrays work

FILTER

Enter this formula in E2:

=IF(NOT(ISNUMBER($C$2)),"Invalid threshold",FILTER(A2:B5,ISNUMBER(B2:B5)*(B2:B5>=$C$2),"No matches"))

Expected result in E2:F3:

This formula first checks that C2 contains a number. Within the filter condition, ISNUMBER(B2:B5) excludes malformed amount entries.

Next, enter these controls outside the spill range.

In K1:

Control

In K2:

=SUM(B2:B5)

Expected result: 400.

In K3:

=SUM(F2:F3)

Expected result: 320.

In K4:

=ROWS(E2#)

Expected result: 2.

Together, these controls confirm that the inputs total USD 400 and that the 2 filtered rows total USD 320.

SORT

After clearing the FILTER formula and E2:F10, enter this formula in E2:

=SORT(A2:B5,2,-1)

Expected result in E2:F5:

The second argument selects the second column as the sort index, while -1 requests descending order.

UNIQUE

Once you’ve cleared the SORT formula and its output, enter:

=UNIQUE(A2:A5)

Expected result in E2:E4, in first-occurrence order:

Finance
Ops
Sales

The duplicate Finance entry appears only once.

SEQUENCE

Clear the UNIQUE formula and its output, then enter:

=SEQUENCE(4,1,1,1)

Expected result in E2:E5:

1
2
3
4

These arguments request 4 rows, 1 column, a starting value of 1, and an increment of 1.

Cause 1: A cell is blocking the spill range

A dynamic-array formula won’t overwrite existing contents. A constant, formula, space character, formula returning "", or another array can obstruct the result. Formatting by itself doesn’t make a cell nonblank.

Test the blocked-cell case:

1. Clear E2:F10.
2. Confirm that E2:F10 is unmerged.
3. Enter Budget in E3.
4. Enter the FILTER formula in E2.

The formula requires E2:F3, which places E3 inside its intended output area. Expected result: E2 shows #SPILL!.

Select E2 to inspect the indicated spill range. Open the error menu and, when available, choose Select Obstructing Cells. Excel should identify E3.

Before deleting Budget, check whether any formulas depend on E3:

1. Select E3.
2. Use Formulas > Trace Dependents.
3. If E3 is needed elsewhere, move either its content or the FILTER formula.
4. Otherwise, clear E3.

Once E3 is blank, E2:F3 should contain the two Finance rows totaling USD 320.

Cause 2: The destination contains merged cells

A spilled array can’t populate a range containing merged cells.

To reproduce the conflict:

1. Clear E2:F10.
2. Merge E3:F3.
3. Enter the FILTER formula in E2.

Expected result: E2 shows #SPILL! because the required E2:F3 output intersects the merged area.

Use Home > Merge & Center > Unmerge Cells on E3:F3. Afterward, confirm that E2:F3 contains the two expected FILTER rows.

Before continuing, delete the formula in E2, clear E2:F10, and confirm that E3:F3 is unmerged. This reset is required even if you moved the formula instead of unmerging the original destination.

Cause 3: The formula is inside an Excel Table

Excel Tables can provide source data for dynamic-array formulas, but spilled-array formulas must be placed outside the table. Microsoft explains this restriction in its documentation on dynamic-array formulas and spilled-array behavior.

Use a separate area so this test doesn’t overwrite Threshold_USD:

1. Copy A1:A5 as values into H1:H5.
2. Enter UniqueDepartment in I1 and leave I2:I5 blank.
3. Select H1:I5, create an Excel Table, and name it InputTable.
4. Confirm that its first column is named Department.
5. Enter this formula in I2, inside InputTable:

=UNIQUE(InputTable[Department])

Expected result: #SPILL! because the formula attempts to return multiple rows inside a table column.

Now clear E2:F10 and enter the same formula in E2, outside InputTable:

=UNIQUE(InputTable[Department])

Expected result in E2:E4:

Finance
Ops
Sales

The source data can remain in InputTable. Only the spilled output formula needs a clear, normal range outside it.

Before the next test, clear the formula from the UniqueDepartment table column, including any populated calculated-column cells in I2:I5. Then delete the formula in E2 and clear E2:F10.

Cause 4: The result crosses the worksheet boundary

An Excel worksheet ends at row 1,048,576 and column XFD, as listed in Microsoft’s Excel specifications and limits.

Enter this formula in XFD2:

=SEQUENCE(1,2,1,1)

The formula requests 1 row and 2 columns. Because XFD is the final worksheet column, the second value has nowhere to go. Expected result: #SPILL!.

Move the formula, rather than copying it, from XFD2 to XFC2. Expected result:

If you left the original formula in XFD2, it would obstruct the relocated array. The same boundary problem can occur near the bottom of a worksheet or when a formula unexpectedly returns an entire column.

After checking the result, delete the formula from XFC2.

Cause 5: Two dynamic arrays overlap

One spilled array can block another.

Reproduce the overlap:

1. Confirm that E2:E10 is unmerged and empty.
2. Enter this formula in E4:

=SEQUENCE(3,1,1,1)

It spills 1, 2, and 3 into E4:E6.

3. Enter the same formula in E2:

=SEQUENCE(3,1,1,1)

The second formula needs E2:E4, but E4 is already the anchor of the first formula. Expected result: E2 shows #SPILL!.

Delete the anchor formula in E4 or move that array elsewhere. Once you remove the conflict, the formula in E2 should return 1, 2, and 3 in E2:E4.

Don’t try to delete a spill child cell individually. Select the anchor containing the formula, then move or remove the complete array.

Cause 6: Excel can’t determine a stable array size

A formula’s required spill dimensions may change between calculation passes when a volatile function controls its dimensions. Microsoft calls this an indeterminate spill range.

For example:

=SEQUENCE(RANDBETWEEN(1,1000))

RANDBETWEEN can change the requested number of rows during recalculation. The formula isn’t guaranteed to fail on every calculation, but its output dimensions aren’t stable enough for predictable results.

Instead, use a validated worksheet input. Enter this formula in E2:

=IFERROR(IF(AND(ISNUMBER($D$2),$D$2>=1,$D$2=INT($D$2)),SEQUENCE($D$2,1,1,1),"Invalid row count"),"Invalid row count")

With D2 set to 4, the expected result in E2:E5 is:

1
2
3
4

The formula defines Row_Count as a positive whole number. Test the remaining input cases separately:

Restore D2 to 4 after completing these checks.

Cause 7: Excel runs out of calculation resources

Microsoft also documents an out-of-memory form of #SPILL!. It differs from crossing the worksheet edge because the output may fit on the sheet but still require more calculation resources than Excel can provide.

There isn’t a dependable small example for reproducing this error. The threshold depends on the workbook, formulas, Excel environment, and available resources. Diagnose it by reducing the calculation scope:

• Replace full-column array references with bounded ranges when the data extent is known.
• Return only the rows and columns required for the analysis.
• Aggregate transaction data before displaying it when row-level output isn’t needed.
• Close unrelated large workbooks and recalculate.
• Split a complex formula into intermediate calculations when that helps isolate the demand.

Don’t treat a specific row count as a universal limit. Resource requirements depend on the formula and workbook, not only on the number of output cells.

Check FILTER input and boundary cases

Reset each input as directed so one test doesn’t affect the next.

Zero threshold

Set C2 to 0 and enter the FILTER formula in E2:

=IF(NOT(ISNUMBER($C$2)),"Invalid threshold",FILTER(A2:B5,ISNUMBER(B2:B5)*(B2:B5>=$C$2),"No matches"))

Expected result: all 4 input rows, including Sales | 0.

Blank or text threshold

Clear C2 so it’s truly blank and recalculate. Next, enter the text high in C2 and recalculate again.

Expected result in both cases:

Invalid threshold

The ISNUMBER($C$2) control distinguishes an invalid threshold input from a valid calculation with no matches.

Malformed amount

Restore C2 to 100. In a test copy, replace B3 with the text bad.

Expected result: the Ops row is excluded because ISNUMBER(B3) is false. The two Finance rows still qualify:

Restore B3 to 80 after this check.

No matching rows

Set C2 to 500.

Expected result:

No matches

That text comes from FILTER’s third argument. It distinguishes a valid calculation with no qualifying rows from an invalid threshold or broken formula.

Restore C2 to 100 after this test.

Check SORT, UNIQUE, and SEQUENCE edge cases

SORT with zero

Confirm that B3 is 80, clear E2:F10, and enter:

=SORT(A2:B5,2,-1)

The expected Amount_USD order is 200, 120, 80, then 0. Blank or text amounts fall outside this example’s numeric-input contract and should be flagged for cleanup instead of being assigned an assumed financial meaning.

UNIQUE with duplicates and a referenced blank

With A2:A5, the two Finance entries produce one Finance result.

Next, confirm that A6 is truly blank and set E2:E5 to the General format. Enter:

=UNIQUE(A2:A6)

For this setup, the expected displayed result in E2:E5 is:

Finance
Ops
Sales
0

The final 0 represents the blank cell referenced by the array. It isn’t a department value. To remove blank departments explicitly, use:

=UNIQUE(FILTER(A2:A6,A2:A6<>""))

Expected result:

Finance
Ops
Sales

SEQUENCE starting from zero

Clear E2:F10 and enter:

=SEQUENCE(4,1,0,1)

Expected result:

0
1
2
3

The starting value is 0, and each subsequent row increases by 1.

How to prevent recurring #SPILL! errors

Reserve predictable output areas for dynamic-array formulas:

• Keep the expected spill range empty and unmerged.
• Put dynamic-array formulas outside Excel Tables.
• Separate variable-size outputs from assumption and reporting blocks.
• Check whether the maximum expected dimensions fit within the worksheet.
• Avoid placing independently expanding arrays where their possible ranges intersect.
• Validate inputs that control FILTER conditions or SEQUENCE dimensions.
• Recheck the output range after source data expands.

You can refer to a completed spilled array with the # operator. With the baseline FILTER formula in SpillDemo!E2, enter this control in SpillDemo!K4:

=ROWS(E2#)

Expected result: 2.

To test a cross-sheet spill reference:

1. Create a worksheet named Review.
2. Confirm that Review!A1:B10 is empty and unmerged.
3. With the baseline FILTER formula working in SpillDemo!E2, enter this formula in Review!A1:

=SpillDemo!E2#

Expected result in Review!A1:B2:

The reference expands and contracts with the array anchored in SpillDemo!E2. It returns an error if the anchor formula can’t spill.

For a final control:

1. Restore B3 to 80, C2 to 100, and D2 to 4.
2. Clear the formula from InputTable[UniqueDepartment].
3. Clear E2:F10 and confirm that it contains no merged cells.
4. Run the 4 baseline formulas separately, deleting the previous anchor before each test.

If your reporting workflow also uses Google Sheets, don’t assume its array behavior matches Excel. See Google Sheets AI formula tools for FP&A for platform-specific formula guidance.

Inspecting a spill error with ModelMonkey

ModelMonkey is an AI assistant for spreadsheet work in Excel and Google Sheets. It can inspect workbook context, read ranges, and help analyze formula errors.

For range and formula-error inspection, use a specific request such as:

Inspect the formula error in SpillDemo!E2 and read E2:F10. Identify nonblank cells or formulas that could obstruct the output. Based on the workbook context available to you, tell me whether I should also check manually for merged cells, an Excel Table boundary, an overlapping array, or a worksheet-edge conflict. Explain the proposed fix without removing inputs used by other formulas.

Whether merged areas or Table boundaries are visible to the assistant depends on the workbook context available in the integration. Review the diagnosis and any proposed changes against the expected output and the workbook’s dependency structure. ModelMonkey can help with inspection, but it doesn’t guarantee that every diagnosis or edit will be correct.

Use ModelMonkey to inspect a #SPILL! formula and its output range.