#SPILL! Error in Excel: What It Means and How to Fix It

A #SPILL! error means your formula worked perfectly and then had nowhere to put the answer. Modern Excel formulas such as FILTER, SORT, UNIQUE, and SEQUENCE return multiple results at once and write them into the cells below and to the right of where you typed them. That area is called the spill range, and if anything is in the way, Excel refuses to overwrite it and shows #SPILL! instead. The useful part is that Excel tells you exactly what is blocking it, in a message most people never look at. Here is how to read that message, and the fix for each of the six things it can say.

How to read the #SPILL! error message

Do not start guessing. Click the cell showing #SPILL!, then click the yellow warning triangle that appears beside it. The first line of the dropdown is a specific diagnosis, and it will be one of six messages. Each has a different cause and a different fix.

The dropdown also contains Select Obstructing Cells on the relevant errors, which jumps you straight to the cell causing the problem. On a large sheet this saves more time than anything else in this article.

"Spill range isn't blank"

By far the most common, and the easiest to fix.

Something is sitting in the cells your formula needs. Click Select Obstructing Cells in the warning dropdown, and Excel highlights the culprit. Then go to the Home tab, Editing group, and click Clear, then Clear All.

The frustrating version of this error is when the range looks completely empty. Two things hide in apparently blank cells:

A formula returning an empty string. A cell containing =IF(A1>5,"Yes","") looks blank but is not. Excel counts it as occupied.

A stray space character. Invisible, and very common in imported data.

To find either, run =ISBLANK() or =LEN() against a suspect cell. A truly empty cell returns TRUE or 0. Anything else means something is in there. Ctrl + H and a find-and-replace across the range clears stray spaces in one pass.

"Spill range has merged cell"

Merged cells block spilling even when they contain nothing at all. Excel cannot write individual values into a merged block.

Select the spill range, go to the Home tab, and click the Merge & Center dropdown, then Unmerge Cells. If you cannot see which cells are merged, Select Obstructing Cells finds them.

If you merged those cells for a heading and want to keep the look, use Center Across Selection instead. Select the cells, press Ctrl + 1, go to Alignment, and choose Center Across Selection under Horizontal. It looks identical and does not break formulas. Merged cells cause problems well beyond dynamic arrays, so this is a good habit generally.

"Spill range in table"

Dynamic array formulas do not work inside Excel Tables. Tables have a fixed structure and dynamic arrays change size, so the two are fundamentally incompatible.

Two fixes. Move the formula outside the table, into an ordinary cell on the worksheet, which is usually the better answer because it keeps the table intact. Or convert the table to a normal range: click inside it, go to Table Design, and click Convert to Range in the Tools group.

Note that a formula can reference a table perfectly well from outside it. It is only placing the formula inside the table that fails.

"Spill range is too big"

Your formula is trying to return more results than the worksheet can hold. Excel tops out at 1,048,576 rows and 16,384 columns.

The usual cause is a whole-column reference. A formula like =C:C*20% asks Excel to return over a million results, and if it cannot fit them starting from your chosen cell, it fails.

Fix it by bounding the range. =C2:C500*20% instead of =C:C*20%. If you want the range to grow automatically as data is added, either reference a table column or wrap the range so it ends where your data ends rather than at the bottom of the sheet.

"Spill range is unknown"

Excel cannot work out how large the result will be before calculating it. This almost always involves a volatile function such as RAND, RANDARRAY, or RANDBETWEEN nested inside another function, where the inner result changes between recalculations and the outer function cannot pin down a size.

There is no setting to fix this one. Restructure the formula so the size is determined by something stable, or calculate the random values into their own range first and have the second formula reference that range.

"Out of memory"

Rare, and usually the sign of a formula asking for far more than intended, often a whole-column reference combined with a lookup. Reduce the ranges involved. If the formula is genuinely necessary at that scale, splitting the calculation into stages usually resolves it.

One thing that is not a #SPILL! problem

If you are on Excel 2016 or 2019, dynamic array formulas are not supported at all. You will not see a #SPILL! error, you will see #NAME? or a single result instead of an array, because those versions do not recognize the functions. Dynamic arrays require Microsoft 365 or Excel 2021 and later.

Preventing #SPILL! errors

Three habits eliminate most of them.

Leave room. When you write a formula that returns multiple results, leave empty rows below and columns to the right, and leave more than you currently need so the range can grow as your data does.

Do not merge cells in working ranges. Use Center Across Selection when you want the visual effect.

Keep dynamic arrays out of Tables. Place them in adjacent cells and reference the table instead.

When an error message is precise but unhelpful, which describes most of Excel's, the gap is knowing what it means in your sheet rather than in general. Guidy reads your actual spreadsheet, explains what the error is pointing at, and walks you through the fix cell by cell rather than describing it in the abstract. That is the case it was built for, and it is covered in more depth on our AI for Excel page.

Key takeaways

• A #SPILL! error means your formula worked and had nowhere to put the answer. Excel names the exact cause behind the yellow warning triangle beside the cell, and it will be one of six messages.
• Select Obstructing Cells in that same dropdown jumps you straight to the blocking cell, which saves more time on a large sheet than anything else.
• Cells that look empty often are not. A formula returning "" or a stray space counts as occupied. Test a suspect cell with =ISBLANK() or =LEN().
• Merged cells block spilling even when they contain nothing. Use Center Across Selection (Ctrl + 1, then Alignment) for the same visual result without breaking formulas.
• Excel tops out at 1,048,576 rows and 16,384 columns, so whole-column references such as =C:C*20% are the usual cause of "spill range is too big." Dynamic arrays also require Microsoft 365 or Excel 2021 and later.

Frequently asked questions

What is the spill range in Excel?

It is the block of cells a dynamic array formula writes into, starting at the cell you typed the formula in and extending down and to the right as far as the results need. Select the formula cell and Excel outlines the range with a border. If anything occupies any cell inside that block, the formula returns #SPILL! rather than overwriting it.

How do I fix a #SPILL! error?

Click the cell, then click the yellow warning triangle beside it to read the specific cause. Excel reports one of six messages, each with its own fix: clear the obstructing cells, unmerge cells, move the formula out of a table, bound an oversized range, restructure a formula with an unpredictable size, or reduce the calculation.

Why do I get a #SPILL! error when the cells look empty?

Because they probably are not. A formula returning an empty string appears blank but occupies the cell, and stray space characters are invisible. Test with =ISBLANK() or =LEN(), and use Select Obstructing Cells in the warning dropdown to jump straight to the culprit.

Why does #SPILL! appear in an Excel table?

Dynamic array formulas are not supported inside Excel Tables, because tables have a fixed structure and dynamic arrays resize. Move the formula outside the table, or convert the table to a range using Table Design, then Convert to Range.

Can I turn off the #SPILL! error?

Not directly, and you would not want to, since it is preventing data loss. If you only need the single result on the current row rather than the full array, prefix the reference with the @ operator, which tells Excel to return one value instead of spilling.