#SPILL! is one of the errors introduced with Modern Excel's dynamic-array calculation engine.

It does not necessarily mean the formula itself is wrong.

It usually means Excel successfully determined that the formula should return multiple values but could not place those values into the worksheet.

Functions such as:

FILTER

SORT

SORTBY

UNIQUE

SEQUENCE

CHOOSECOLS

VSTACK

HSTACK

and many other Modern Excel formulas can return arrays containing multiple rows, multiple columns, or both.

Instead of requiring you to copy the formula into every destination cell, Excel places the formula in one anchor cell and spills the results into the neighboring cells.

For example:

=SEQUENCE(5)

entered in A2 produces:

1
2
3
4
5

across the range A2:A6.

Only A2 actually contains the formula.

The other cells belong to its spill range.

When Excel cannot create that complete output range, it returns #SPILL!.

The spill range isn't blank

This is probably the first thing to check.

Suppose you enter:

=UNIQUE(A2:A100)

in D2.

Excel determines that the formula needs ten rows.

It therefore needs:

D2:D11

If D8 contains something—even a value you did not notice—the formula cannot occupy the entire range.

Excel returns:

#SPILL!

Click the formula cell and Excel will show an outline around the area it is attempting to use.

That gives you an immediate diagnostic:

What is inside the intended spill range?

Clear or move the obstructing value and the array should spill automatically.

A cell can look blank without actually being blank

This is worth checking carefully.

A cell may appear empty but still contain:

  • A space
  • An apostrophe
  • A formula returning something unexpected
  • Hidden data
  • Another object or value you overlooked

If Excel says the spill range is blocked, inspect the cells inside the outlined spill area rather than relying only on how the worksheet looks.

Merged cells can block a spill

Dynamic arrays cannot spill through merged cells.

For example:

=FILTER(A2:D100,B2:B100=F2)

might need to return a four-column result.

If one of the destination cells is part of a merged range, Excel cannot construct the array normally.

You can either:

  • Unmerge the cells, or
  • Move the formula to an area without merged cells.

This is another reason I generally avoid merged cells inside areas intended for dynamic calculations.

Dynamic-array formulas cannot spill inside an Excel Table

This is an important Modern Excel distinction.

Excel Tables are excellent source structures.

For example, a Table named Sales can be referenced with:

=FILTER(Sales,Sales[Region]=H2)

But the FILTER formula itself should be placed outside the Table.

Dynamic-array formulas cannot spill across multiple cells from inside an Excel Table.

So this architecture works well:

Excel Table

↓

Dynamic-array formula outside the Table

↓

Spilled output

The Table acts as the structured source.

The worksheet grid provides space for the dynamic result.

The result may extend beyond the worksheet

Sometimes the problem isn't another cell.

The requested array is simply too large for where the formula is positioned.

For example:

=SORT(A:A)

references the entire Excel column.

That means Excel may attempt to return as many as:

1,048,576

rows.

If you enter that formula in B2, there aren't another 1,048,576 rows available beneath it.

The requested output would extend beyond the worksheet.

Result:

#SPILL!

Instead of using an entire-column reference, use the actual dataset:

=SORT(A2:A1000)

Or, even better, use a structured source such as an Excel Table:

=SORT(Sales[Customer])

This gives Excel a clearly defined array rather than asking it to process the entire worksheet column.

The size of the array may be changing

A less common #SPILL! occurs when Excel cannot determine how large the resulting array should be.

Consider:

=SEQUENCE(RANDBETWEEN(1,1000))

RANDBETWEEN can produce a different number every time Excel recalculates.

The first calculation might request:

247 rows.

The next might request:

831.

Then:

122.

If the size keeps changing while Excel is attempting to determine the spill range, Excel may eventually return #SPILL!.

In this situation, the issue isn't a blocked cell.

The size of the output itself is unstable.

Very large arrays can create resource problems

Dynamic arrays are powerful, but they still consume memory.

A formula attempting to generate or process an extremely large array can eventually exceed the resources Excel can allocate to the calculation.

This is less common than a blocked spill range, but if the formula is operating over enormous arrays, consider whether the references can be narrowed.

Instead of:

A:A

use:

A2:A50000

or use structured references that naturally follow the size of the source data.

Don't automatically hide #SPILL! with IFERROR

You could write:

=IFERROR(FILTER(A2:D100,B2:B100=F2),"")

But if the actual problem is a blocked spill range, suppressing the error does not fix the design.

#SPILL! is useful information.

Excel is telling you:

I know what I want to return, but I don't have a valid place to put it.

That's different from a formula returning no matches.

For example:

=FILTER(A2:D100,B2:B100=F2,"No records found")

handles the case where FILTER finds no matching records.

It does not solve a physical blockage in the destination range.

Treat those as different problems.

The anchor cell controls the spill

When a dynamic-array formula spills, only the first cell actually contains the editable formula.

For example, if:

D2

contains:

=UNIQUE(A2:A100)

and the results spill through:

D2:D15

then D2 is the anchor.

You cannot independently edit D7.

That cell belongs to the array produced by D2.

To change the spilled result, change the formula in the anchor cell.

This is an important shift from traditional Excel.

Older workbook designs often relied on one independent formula per row.

Modern Excel allows one formula to generate an entire output structure.

Spilled ranges can become references themselves

Once a formula successfully spills, the entire output can be referenced with the # operator.

If the formula begins in:

D2

then:

D2#

means:

the entire current spilled array beginning at D2.

So another formula could use:

=SORT(D2#)

or:

=COUNTA(D2#)

This makes dynamic arrays especially useful when building application-style Excel workbooks.

The result can expand or contract without constantly rewriting fixed ranges.

What to check when you see #SPILL!

Start with the physical output area.

Ask:

  • Is anything inside the intended spill range?
  • Are there merged cells?
  • Is the formula inside an Excel Table?
  • Is the array trying to extend past the worksheet edge?
  • Am I referencing an entire column unnecessarily?
  • Is a volatile formula causing the output size to keep changing?
  • Is the requested array unnecessarily large?

In most cases, clicking the #SPILL! cell and looking at Excel's outlined intended spill range will tell you where to start.

The bigger lesson

#SPILL! is different from many traditional Excel errors.

A #REF! may tell you a reference no longer exists.

A #N/A may tell you a lookup could not find something.

A #VALUE! may indicate that Excel received an unexpected type of value.

#SPILL! often tells you something about the architecture of the worksheet itself.

The formula has an output.

It just doesn't have somewhere valid to put it.

That's why dynamic-array workbooks benefit from deliberately separating:

Source Data

from

Transformation Logic

from

Spilled Outputs

Give dynamic formulas room to expand.

Use Tables to structure your source data.

Use the worksheet grid for dynamic outputs.

And treat spilled ranges as components that can grow and shrink as the underlying data changes.

Once you start designing around that behavior, #SPILL! becomes much easier to diagnose—and much easier to avoid.