Excel Spill Error: Causes, Fixes, and Clear Examples

To fix an Excel spill error, select the cell showing #SPILL!, identify what is blocking the formula’s intended output range, and clear or move the obstruction. Dynamic array formulas need room to return results in neighboring cells; once that space is available, Excel can display the full result.

What the Excel spill error means

In Excel for Microsoft 365, many formulas can return several values at once. Instead of showing only one result in the formula cell, Excel places the remaining results in adjacent cells. This behavior is called spilling, and the formula’s starting cell is its anchor cell. A #SPILL! error means Excel cannot place the array where the formula expects to put it.

The intended output cells are called the spill range. When a formula can spill successfully, Excel outlines that range when you select its anchor cell. You can edit the formula in the anchor, but the other cells contain results supplied by the formula; they are not separate formula cells. Clearing an individual result cell is not a way to change that result—the formula determines it.

For example, with D2 clear and enough worksheet space below it, this formula in D2 returns the numbers 1 through 5 in D2:D6:

With D2 empty and no obstructions in D2:D6, enter this formula in D2: =SEQUENCE(5). The result is 1, 2, 3, 4, and 5, one number per row.

SEQUENCE is available in Excel for Microsoft 365 and Excel 2021 or later. Other common dynamic array functions include FILTER, SORT, and UNIQUE. Their results can vary in size, so a spill range that was clear yesterday may be blocked after someone enters data there today.

Start with a quick diagnosis

Do not delete the formula as your first response. Excel often identifies the problem and the cell or cells responsible. Try this sequence in Excel for Microsoft 365 on Windows:

Select the cell displaying #SPILL!.
Look for the warning icon next to the cell and open its menu. Choose Select Obstructing Cells if that option is offered.
Check the highlighted cells and decide whether their contents can be cleared or moved. Inspect the formula bar too; a cell that looks blank may contain a space or a formula.
If the formula’s results are meant to go somewhere else, move the formula to a clear area rather than removing useful data.
After removing or relocating the obstruction, confirm that Excel has recalculated and the intended results appear.

If the warning menu is not available, select the anchor cell and inspect the outlined spill range. Compare that outline with the cells around it, including rows and columns that may be hidden. On Mac and Excel for the web, the formula behavior is the same, although the warning control’s position and appearance can differ. Select the error cell and look for its error indicator or contextual warning menu; if the obstruction-selection command is unavailable, inspect the outlined range manually.

Find what is blocking the spill range

A spill range must be clear of content. The obstruction does not have to be visible text: a number, a formula, a space, or another value can prevent Excel from placing an array in that cell. A formula that displays an empty string can also occupy a cell even though it looks blank. Formatting alone, such as a fill color or a border, does not normally block a spill.

When a cell appears empty, select it and check the formula bar. If it contains a space or unwanted formula, remove it only after confirming that doing so will not damage other work. You can also copy the formula to a genuinely empty area as a diagnostic test. If it works there, the original location likely has an obstruction or a layout constraint.

Look beyond the first cell where you expected a result. A formula may return more rows or columns than it did previously, especially when its source data changes. For instance, adding another matching record can expand a FILTER result into a cell that already contains a note. Clearing just the first visible blocker may expose a second one farther down or across the range.

Hidden rows and columns deserve attention because they can make existing contents easy to miss. Unhide the relevant area temporarily if needed, or use Excel’s obstruction-selection command to locate occupied cells. Do not assume that a blank-looking grid area is empty until you have checked its contents.

Common causes and their fixes

Occupied cells: Move or clear values, formulas, spaces, or other content in the proposed output range. Preserve anything important by moving the formula or the existing data instead of deleting it.
Merged cells: A spill range cannot include merged cells. Unmerge the cells in the intended output area, or move the formula to a region without merged cells. In Excel for Windows, the relevant command is Home > Merge & Center; use its menu to unmerge when appropriate.
Formula entered inside an Excel table: Dynamic array formulas cannot spill from within a table. Put the formula in an ordinary worksheet cell outside the table, leaving space for its results. The formula can still refer to table data.
Spill range extends beyond the worksheet: A result cannot spill past the last row or column. Move the formula to a location with enough room, or adjust the formula so it returns fewer rows or columns.
Very large or resource-intensive output: A formula that attempts to return an unusually large array may fail because Excel cannot produce the requested result. Reduce the source range or filter the data to the records actually needed.

These are different from ordinary calculation errors. A misspelled function or invalid argument may produce another error, while #SPILL! specifically concerns placing an array result. Fixing a spill obstruction will not correct a separate problem in the formula itself.

Check the formula’s expected size

Some spill errors are caused by a formula returning more output than its author expects. Check the function’s inputs and criteria, then estimate how many rows and columns its result can occupy. For a fixed-size result such as a sequence, the dimensions are explicit. For a filter, the number of matching records can change as the source data changes.

For example, suppose A2:B10 contains city names in column A and region names in column B, and E1 contains the region to find. With those cells set up, enter this formula in D2, outside any table, and leave room below and to the right:

With city and region records in A2:B10 and the requested region in E1, enter in D2: =FILTER(A2:B10,B2:B10=E1,"No matches"). Excel returns the matching rows from columns A and B, or the text “No matches” if there are none.

This formula can spill into two columns, not just one, because its source array A2:B10 has two columns. It can also return multiple matching rows. Check both the vertical and horizontal space around D2. FILTER requires Excel for Microsoft 365 or Excel 2021 and later; older versions may not support it.

If your goal is to limit the result, change the formula’s source or criteria rather than deleting results after the fact. A formula that returns fewer rows may be more reliable in a crowded report, but make sure the revised criteria still include all records the report is supposed to show.

Tables, merged cells, and worksheet layout

Excel tables are useful for organizing data and automatically extending formulas and formatting, but they have a special limitation: a dynamic array result cannot spill within the table’s cells. Keep the source data in a table if that is helpful, and place the array formula in the regular worksheet grid outside the table. Leave a clear area large enough for its output.

Merged cells create a different layout conflict. Even if the merged area looks like a single empty cell, a spill range cannot pass through it. Unmerging may alter the appearance of a report, so consider moving the formula to a dedicated output area instead. This can preserve a presentation layout while keeping the dynamic results in a normal grid.

Planning the output area is especially helpful in shared workbooks. If a spill formula is expected to return a changing number of rows, avoid putting notes, totals, or manual entries directly beneath it. Put explanatory text elsewhere, or use a separate sheet for results. Remember that adding records to the source can enlarge a spill range later.

What does not fix a spill error

Changing a cell’s number format, font, or color does not remove content from a blocked cell. Likewise, pressing Delete on the formula’s anchor cell removes the formula itself, not just the error. If the formula is correct, retain it and address the cells or layout constraints in its intended output range.

Another common misconception is that every error involving a dynamic array is a spill error. #SPILL! concerns space or placement. #CALC! can indicate a calculation issue, while errors such as #VALUE! may relate to the formula’s inputs or operations. Read the exact error shown and check the formula before changing source data.

Also, the spill-range reference operator does not clear a blocked range. For example, a reference such as D2# refers to the complete array produced by the formula in D2 when that array exists; it is not a command to make more space. Resolve the original spill issue first.

Prevent future spill errors

A few design habits make dynamic array formulas easier to maintain, especially when several people edit a workbook:

Place spill formulas in open worksheet areas, not inside tables or merged layouts.
Reserve space for results that may grow, including extra columns when the formula returns records from multiple fields.
Keep manual notes and totals outside the expected spill range.
Use clearly labeled output sections, and explain which cells are generated by a formula rather than intended for manual entry.
When source data changes, review formulas that return a variable number of rows, such as filters and unique-value lists.

These practices do not guarantee that an error can never occur: another editor may still place data in the reserved area. They do make the intended layout clearer and reduce the chance that useful information will be overwritten or removed while troubleshooting.

Frequently Asked Questions

How do I find the cell causing #SPILL!?
Select the error cell and open its warning menu. If available, choose Select Obstructing Cells. Otherwise, inspect the outlined spill range and check cells that look blank for spaces, formulas, or hidden contents.

Can formatting cause a spill error?
Formatting such as color, borders, or number formats does not normally block a spill. Cell contents, merged cells, table placement, and worksheet boundaries are more likely causes.

Why does a blank cell still block a formula?
It may not actually be empty. A space, a value hidden by formatting, or a formula returning an empty string can occupy the cell. Select it and inspect the formula bar before clearing anything.

Can a dynamic array formula spill inside an Excel table?
No. Put the formula in a normal worksheet cell outside the table, with room for its output. It can still use the table as a data source.

Does #SPILL! mean my formula is wrong?
Not necessarily. It often means the formula is valid but Excel cannot place its results in the intended cells. If clearing the obstruction does not help, check for merged cells, a table, a worksheet edge, or a separate formula error.

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.