Fix the #SPILL! Error in Excel

#SPILL! means the formula worked and had nowhere to put the answer. Something is sitting in the cells it needed.

Click the cell, then click the warning triangle beside it. Excel names the specific reason — and there are six, with different fixes. Most people never open that menu and start guessing instead.

The formula asked for ten rows and one of them was occupied. Excel names the reason and offers to select the cell in the way.

What a spill is

Modern Excel formulas can return more than one value. =SEQUENCE(10) returns ten numbers, =UNIQUE(A2:A500) returns however many distinct values it finds, =FILTER(...) returns however many rows match.

You write the formula in one cell and the results spill into the cells below or beside it. A blue border marks the spilled area. Only the top-left cell contains a formula; the rest are its output.

If any of those cells isn't empty, nothing spills, and you get #SPILL! instead.

⚠️ This error only exists in Microsoft 365 and Excel 2021. Excel 2019 and earlier have no dynamic arrays, so they can't produce it — and can't run the formulas that cause it either.

The six causes

Excel says	Fix
Spill range isn't blank	Clear the blocking cells
Spill range has merged cells	Unmerge them
Spill range is in a table	Convert the table to a range
Spill range is unknown	Rewrite the formula so its size is predictable
Spill range is too big	Bound the input range
Out of memory	Reduce what the formula returns

1. Something is in the way

The common case, and the frustrating part is that the obstruction is often invisible — a single space someone typed years ago, or a cell holding "" returned by an old formula.

Excel will show you exactly which cell is at fault. In that same warning menu, click Select Obstructing Cells — it selects them for you, however invisible their contents.

Then press Delete. Not the spacebar, which replaces one invisible obstruction with another.

That menu item is the whole answer, and it's the reason to open the dropdown rather than start hunting. If you want to know what was in the cell before removing it, =LEN() pointed at it will return 1 where a stray space is the culprit.

2. Merged cells

A spill can't write into a merged cell, and merged cells break sorting, filtering and most formulas as well. Unmerge them and use Center Across Selection if you only wanted the visual effect.

3. The formula is inside a Table

Excel Tables predate dynamic arrays and don't support them. A spilling formula in a Table column returns #SPILL! however empty the cells are.

Two options: move the formula outside the Table, or select any cell in it and use Table Design → Convert to Range. You lose the Table's automatic expansion, which is a real cost — so moving the formula is usually better than converting.

4. The size can't be predicted

Excel needs to know how large the result will be before it calculates. A formula whose size depends on something unpredictable can't be resolved:

=SEQUENCE(RANDBETWEEN(1,10))

Fix it by making the size deterministic — reference a cell holding the count rather than generating it.

5. The spill range runs off the sheet

This one catches people who use whole-column references:

Written in B2:  =A:A*2

That asks for 1,048,576 results starting at row 2 — one row past the bottom of the sheet. Bound the range and it works:

=$A$2:$A$5001*2

The same argument as avoiding whole-column references for performance, arriving from a different direction.

The two symbols worth knowing

The @ sign, when you don't want a spill

Sometimes one value is what you actually want. The @ operator forces it:

=@A:A     one value, the one on this row
=A:A      every value, spilled

You'll also see @ appear on its own in formulas from older files. When Microsoft 365 opens a workbook built in Excel 2016, it inserts @ into formulas that used to rely on implicit intersection, so they keep behaving as they did. That's Excel preserving your results, not corrupting your formulas — leave them alone unless you actually want the spilling behaviour.

The # sign, to reference a whole spill

=SUM(E2#)

E2# means "everything E2 spilled", however many rows that turns out to be today. It grows and shrinks with the result.

This is genuinely useful and badly underused. A chart pointed at E2#, or a total built on it, adjusts itself as the data changes — no ranges to update, nothing to break when the row count moves.

The compatibility trap

Worth knowing before you build anything important on dynamic arrays.

Open a file containing UNIQUE, FILTER, SORT, SEQUENCE or XLOOKUP in Excel 2019 or earlier and the formulas don't degrade gracefully — they appear as _xlfn.UNIQUE and return #NAME?. The results are gone, and saving in the older version can make the loss permanent.

If the file goes to clients, colleagues or anyone whose Excel version you don't control, build it with INDEX, MATCH, SUMIFS and COUNTIFS, all of which have worked since 2007. Keep dynamic arrays for files that stay with you.

Does this work in Google Sheets?

Sheets has had spilling since long before Excel and reports the same problem differently — the cell shows a message about the array result not being expanded because it would overwrite data, rather than a short error code.

The fix is the same: clear whatever is in the way. Sheets has no Table equivalent to block it, and its ARRAYFORMULA, UNIQUE, FILTER and SORT behave much like Excel's.

When it goes wrong

The blocking cells look completely empty

They hold a space, or "" from another formula. Select the range and press Delete.

It came back after I cleared the cells

Something is writing into that range — usually another spilling formula overlapping it, or a refresh that repopulates the area. Two spills can't share cells.

The formula worked yesterday

The result got bigger. A UNIQUE returning 40 values where it used to return 30 now needs ten more rows, and something is sitting in them. Leave room below spilling formulas.

#NAME? instead of #SPILL!

The file is open in a version without dynamic arrays. That's the compatibility problem above, not a spill problem.

I can't delete part of the spilled result

Correct — a spill is one object. Delete the top-left formula and the whole thing goes; the other cells can't be edited individually.

Common questions

How do I stop a formula spilling at all?

Wrap it in something that returns one value — SUM, COUNTA, TEXTJOIN — or prefix with @. Ask what single answer you actually want.

Can I fill a spilling formula down a column?

You don't need to, and mostly you can't. One formula produces the whole column of results. Filling it down is the old habit that dynamic arrays remove.

Why is only the first cell editable?

Because only the first cell contains a formula. The rest are its output — which is also why the result stays correct when the underlying data changes shape.

Can a spill go sideways?

Yes. =SEQUENCE(1,10) spills across ten columns, and =TRANSPOSE() turns a column into a row. The same blocking rules apply in both directions.

Should I be using dynamic arrays at all?

For your own files, yes — they're simpler and more robust than filling formulas down. For anything shared into an environment you don't control, no. That's a distribution decision rather than a technical one.

The short version

• #SPILL! means the answer had nowhere to go, not that the formula is wrong.
• Click the warning triangle — Excel names which of the six causes it is.
• The dashed border marks the blocked range. A single space is enough to block it.
• Tables and merged cells refuse spills outright.
• =A:A*2 can't fit; bound the range.
• @ forces one value; # refers to the whole spilled range.
• Leave empty rows below a spilling formula — results grow.
• Dynamic arrays break in Excel 2019 and earlier. Don't build shared files on them.

Next steps: the whole-column habit behind the too-big spill is also the main cause of a slow workbook, and if you're avoiding dynamic arrays for compatibility, INDEX MATCH and COUNTIF for duplicates do the same jobs in any version. A different error with a very different meaning is #N/A, which is often the correct answer.