Excel Spill Error: Causes and Ways to Fix It

The Excel #SPILL! error means a formula is trying to return results into multiple cells, but Excel cannot place one or more results in the required spill range. The usual fix is to remove whatever is blocking that range; other causes include merged cells, placing an array formula inside an Excel table, or a result that is too large or cannot be sized.

What a spill error means

In Microsoft 365 and newer Excel versions, many formulas can return an array of results rather than a single value. Excel puts the formula in one cell, called the anchor cell, and fills the neighboring cells with the results. That automatic expansion is called spilling. The anchor contains the formula; the other cells in the output area are populated by Excel and are not separate formulas.

For example, a sorting formula can return an ordered list in several rows. A filtering formula can return several rows and columns from a source range. When Excel cannot use the cells the results need, it displays #SPILL! in the anchor cell. The formula may be valid: the problem is often the space around it, not the calculation itself.

Spill ranges can change size when source data changes. A formula that returned four rows yesterday may need six rows today. Excel therefore checks the required output area whenever it recalculates, and an obstacle added later can cause a spill error even if the formula worked before.

How dynamic array formulas work

Dynamic array behavior is built into functions such as SORT, FILTER, UNIQUE, and SEQUENCE in Microsoft 365 and Excel 2021 or later. Some ordinary formulas can also return arrays in these versions. Enter the formula once in the top-left cell where you want the results to begin; Excel fills the rest of the spill range automatically.

Suppose A2:A5 contains 4, 1, 3, and 2, respectively. With those values in A2:A5, enter =SORT(A2:A5) in C2. The result spills down C2:C5 as 1, 2, 3, and 4. C2 is the anchor cell. If C4 contains a note, Excel cannot place the third result there, so the formula in C2 returns #SPILL!.

You can refer to the entire result using the spill operator: if the formula is in C2, =C2# refers to its current spill range. This is useful in other formulas and charts because the reference grows or shrinks with the result. The operator does not fix a blocked spill; the original formula still needs enough clear space.

Common reasons Excel shows #SPILL!

One or more cells are not empty. A value, a space character, another formula, or even a formula that displays an empty string can occupy a cell Excel needs. A cell can look blank while still containing content.
The output would overlap the formula. If a formula is entered inside the range it is trying to return, Excel cannot spill the results over its own anchor cell. Move the formula or change the source range.
The spill range includes merged cells. Dynamic array results cannot spill into merged cells. Unmerge cells in the required output area, or move the formula to an unmerged area.
The formula is inside an Excel table. Array formulas that need to spill cannot expand from a table cell. Put the formula outside the table, leaving clear cells for its output. You can still refer to table data from a formula outside it.
The result would extend beyond the worksheet. A formula near the bottom or right edge may need more rows or columns than remain available. Move the anchor farther from the edge or reduce the returned array.
Excel cannot determine the result size or allocate it. Some formulas can produce an unpredictable or exceptionally large array. Excel may report an unknown spill range, a range that is too large, or insufficient memory rather than identifying an ordinary blocking cell.

Formatting by itself usually does not block a spill. A fill color, border, or number format in an otherwise empty cell is different from cell content. If the error seems mysterious, inspect the cells for spaces, formulas, or other values rather than simply clearing their formatting.

How to find the cell blocking the spill

Select the cell displaying #SPILL!. Excel outlines the intended spill area with a dashed border when it can identify that area.
Look inside the outlined range for cells that contain content. Pay attention to cells that appear blank: select them and inspect the formula bar, or press Delete to clear their contents if they are safe to remove.
Open the warning icon beside the error, if it appears, and choose Select Obstructing Cells. Excel selects a cell that is preventing the result from spilling. Check the selected cell before deleting anything, since it may contain data you need.
Check whether the anchor or output range is within a table or includes merged cells. Those problems will not necessarily be solved by deleting an apparent blocker.
After clearing the cause, press Enter in the formula cell or allow Excel to recalculate. Confirm that the returned results occupy the intended cells and have not overwritten or displaced other work.

In some cases Excel cannot select a single obstruction, especially when the range is too large or its size is unknown. In that situation, move the formula to a new, empty worksheet area as a diagnostic test. If it spills there, the original location or its surrounding cells are the issue.

Ways to fix the error without losing data

First decide whether the cells in the spill range are supposed to be there. If a note or calculation is important, do not erase it simply to make the error disappear. Move the dynamic array formula to a clear area, move the existing content, or adjust the formula so its output fits the available space. The anchor cell can be moved by cutting and pasting it, but check references and surrounding layout afterward.

If a cell that looks empty contains an unwanted space or old formula, select it and use Delete to clear its contents. The Delete key clears cell contents; it does not remove the worksheet row or column. If the problem is merged cells, select the affected area and use Home > Merge & Center > Unmerge Cells. Unmerging changes the layout, so check how the cells look afterward.

For a table-related error, place the formula in a normal worksheet cell outside the table and leave enough empty rows or columns for the results. For an oversized result, narrow the input range or use a condition that returns fewer records. Avoid moving the formula only one cell away if its output still overlaps existing data.

When the output size is unpredictable, consider whether the formula needs to return every possible result. Restricting source data to the relevant rows or columns can reduce the required space and calculation work. If you need a fixed-size report, another option is to copy the results and paste them as values after they are correct; pasted values no longer resize automatically when the source changes.

Spill formulas and Excel tables

Excel tables are useful because they expand with data and provide structured references, but they have different behavior from an ordinary worksheet range. A formula entered in a table column is generally treated as a calculated-column formula, which fills down the table. A dynamic array formula that needs to spill into neighboring cells cannot use that space in the same way.

A practical arrangement is to keep the source records in a table and put the array formula in an empty area outside it. Leave room for the output to grow, and make sure another table, note, or report does not occupy the likely spill range. If the formula returns a variable number of rows, reserve enough space for the largest reasonable result rather than only the current one.

Do not confuse structured references with the spill operator. A structured reference points to table data; the # spill operator after an anchor cell refers to the array currently returned by that formula. Each serves a different purpose, and neither makes a blocked output range available.

#SPILL! compared with other Excel errors

#SPILL! is about placing an array result, not necessarily about whether the values can be calculated. #VALUE!, for example, often indicates an incorrect value or argument for a calculation. #CALC! can appear for certain array calculation problems, including cases where a FILTER formula finds no results and no alternative result was supplied. The exact cause depends on the formula and Excel’s message.

Also distinguish an error from a formula that returns only one result. In older Excel versions, some array formulas required selecting an output range and confirming the formula with Ctrl+Shift+Enter. Dynamic arrays work differently: in supported versions, enter the formula in one cell and let Excel spill the result. A workbook opened in an older version may not support newer dynamic array functions in the same way.

A #SPILL! message does not mean that the formula should be confirmed with Ctrl+Shift+Enter. Identify the reason shown by Excel, then clear or redesign the required output area. If the function itself is unavailable in the Excel version being used, the message or behavior may differ; verify that the workbook is open in Microsoft 365 or a version that supports the function.

Preventing future spill errors

Give array formulas a dedicated output area, especially when results can grow as records are added. Avoid putting notes, totals, or separate formulas directly beneath or beside a spill formula if those cells may later fall inside its output range. Keep dynamic array formulas outside tables when they need to expand, and avoid merged cells in likely output areas.

When designing a shared workbook, document which cells are formula anchors and which areas are expected to fill automatically. This helps another person avoid typing over the output range. If a report must preserve a fixed layout, use a deliberately bounded formula or a separate results sheet rather than relying on a spill range that may expand into nearby content.

Frequently Asked Questions

How do I fix a spill error in Excel quickly?
Select the #SPILL! cell, inspect its dashed output boundary, and clear or move the content blocking that range. If the warning menu offers Select Obstructing Cells, use it to locate a blocker. Also check for merged cells and whether the formula is inside a table.

Why does Excel say spill range is not blank when cells look empty?
A cell can look blank but contain a space, a formula returning an empty string, or other content. Select the cells in the outlined spill range and inspect them. Formatting alone is generally not the cause; hidden or visually empty content is more likely.

Can I use a spill formula inside an Excel table?
A formula that needs to spill cannot expand from a table cell into neighboring cells. Put the formula outside the table and leave an empty output area. The formula can still use data from the table as its source.

Does #SPILL! mean my formula is wrong?
Not necessarily. It often means a valid formula cannot place its results in the required cells. Excel’s error details can identify the specific issue, such as an obstruction, merged cell, or table location. Check the formula itself if the error remains after fixing the reported cause.

Why did a formula that worked before start showing #SPILL!?
The formula may now return more rows or columns because its source data changed, or someone may have entered content in the spill range. A changed table or worksheet layout can also introduce a blocker. Inspect the current result size and the cells around the anchor.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.