Microsoft Excel Spill Error: Causes and Fixes

Microsoft Excel’s #SPILL! error means a formula is trying to return multiple results, but Excel cannot place the results in the cells it needs. Find the blocked or unsuitable spill range, clear or adjust it, and the formula will usually recalculate without the error.

What the #SPILL! error means

In current Excel, many formulas can return an array: a set of values arranged in one or more rows and columns. Excel places the first value in the formula cell and spills the other values into neighboring cells automatically. If Excel cannot use that destination range, it displays #SPILL! in the formula cell.

The formula itself may be valid. The problem is often with the worksheet around it, such as a value in the intended output area. For example, a formula that should return five results cannot spill if one of the five destination cells already contains something. The error is not the same as #VALUE! or #NAME?, which usually point to a problem with a formula’s value or name.

Spilled results belong to the formula in the upper-left cell, sometimes called the anchor cell. You can edit that formula, but you cannot directly edit an individual result cell within its spill range. The range updates when the formula recalculates, so treating spill cells like independently entered values can cause confusion.

How dynamic arrays and spilling work

Dynamic-array formulas can return a result whose size depends on the input data. Excel expands or contracts the spill range as needed. Functions commonly used this way include FILTER, SORT, and UNIQUE. In Microsoft 365, they can save you from copying a formula down a column or across a row.

For example, with product names in A2:A6, entering this formula in C2 returns the names in sorted order, spilling the results below C2: =SORT(A2:A6). If A2:A6 contains Pear, Apple, Lime, Mango, and Banana, the output is Apple, Banana, Lime, Mango, and Pear. The formula is entered once, in C2; the other output cells are controlled by that formula.

A spill range is shown with a colored outline when you select its anchor cell. You can refer to the complete range using the anchor cell followed by the spill operator, #. This is useful when another formula needs all the current results, even if the array grows or shrinks. The operator does not fix an obstruction: the source formula must still be able to spill.

How to find the obstruction

Start with the cell that shows #SPILL!. Select it and inspect the dashed outline Excel displays for the intended spill range. On Windows, select the warning icon beside the cell, if it appears, and choose Select Obstructing Cells to locate cells that block the output. The warning menu may offer different explanations depending on the cause.

Then check the outlined area for values, formulas, spaces, merged cells, or a worksheet boundary. A cell can look empty but still contain a space or a formula that returns an empty string. Select suspicious cells and inspect the formula bar or press Delete to clear their contents. Do not delete cells or shift surrounding data unless you intend to change the layout.

If the outline is difficult to see, click the formula cell and examine the formula bar. Confirm that the formula is the one you expect and check its input ranges. If the formula is meant to return more rows than the available space, move it to a location with room or reduce the result. Also check whether the formula is inside an Excel Table.

Common causes of a spill error

Cells are not truly empty. A constant, space, formula, or other content in any destination cell can block the array. Formatting alone does not normally block a spill; cell contents do.
Merged cells overlap the output area. Dynamic arrays cannot spill into merged cells. Unmerge the affected cells or move the formula to a clear range.
The formula is inside an Excel Table. Dynamic-array formulas are not supported as spilling formulas within table cells. Put the formula in an ordinary worksheet range outside the table, leaving enough space for its results. A formula outside the table can still refer to table data.
The output would run past the edge of the worksheet. Excel cannot spill beyond the last row or column. Move the anchor cell so the complete result fits on the sheet.
The result size is unpredictable during calculation. A formula whose array dimensions change between calculation passes, sometimes because it uses a volatile function, can produce an indeterminate-size spill error. Use inputs that determine a stable result size where possible.
The array is too large or resource-intensive. A formula that attempts to return an extremely large result may exceed what Excel can calculate or display. Avoid unnecessary whole-column inputs for array calculations and use a bounded range when practical.

Ways to fix #SPILL! safely

Clear blocking contents. If the cells are not needed, select only the obstructing cells and press Delete. On Windows, Delete clears contents; it does not remove the cells or shift the grid. On Mac, the Delete key may be labeled Backspace, depending on the keyboard.
Move the formula. If the cells contain data you need to keep, move the formula to a sufficiently large blank area instead. Check the entire possible output range, not just the first few cells.
Unmerge or rearrange cells. If merged cells overlap the spill area, unmerge them or place the formula elsewhere. Consider whether the layout can use alignment or formatting instead of merged cells.
Move the formula outside a Table. Keep the source data in the Table if useful, but put the spilling formula in a regular range. Leave clear cells beside and below it for the full result.
Limit the output. If the formula refers to far more rows than the data uses, adjust its input range or filter the results to what is needed. This can also make calculation more manageable.
Recheck the result. After changing the sheet, select the anchor cell and verify that the full result appears. If the error remains, inspect the updated outline and warning message; there may be more than one obstruction.

Clearing a spill error does not necessarily mean the displayed results are correct for your task. After it resolves, check that the formula’s source range includes the intended records and that the newly populated cells do not overwrite information you meant to keep.

Examples with FILTER and UNIQUE

Suppose A2:A6 contains product names and B2:B6 contains their statuses. Enter the following in D2 to show only products marked Open; the output occupies D2 downward, and the text is returned if there are no matches: =FILTER(A2:A6,B2:B6="Open","No matches"). If the result should contain three names but a value is already in D4, Excel cannot place all three results and may show #SPILL!. Clear D4 or move the formula.

For another example, with city names in A2:A8, enter this in C2 to return each distinct name once: =UNIQUE(A2:A8). If the source contains Boston, Denver, Boston, Austin, Denver, Miami, and Austin, the results are Boston, Denver, Austin, and Miami. Any content in C3:C5 blocks part of that output. These examples assume Microsoft 365 or a version of Excel that supports the functions used.

When the output is meant to change with new records, use a range that includes the relevant data or reference a Table from outside it. A fixed range such as A2:A8 will not automatically include entries added below row 8. This is a range-design issue, not a spill error, but it can make a spill formula appear to miss newer data.

Version and platform differences

Dynamic arrays and the functions in these examples are available in Microsoft 365 and supported newer Excel releases; availability depends on the function and version. Microsoft 365 for Windows is the focus here. Excel for Mac and Excel for the web also support dynamic-array behavior, though the interface for selecting error details or managing cells can differ. The basic remedy is the same: remove the obstruction, make room, or change where the formula spills.

In older Excel versions without dynamic-array support, entering a modern array formula may not behave as described here; updating Excel may be necessary. Conversely, an older workbook can contain legacy array formulas. Those are handled differently from modern spill formulas, so check the formula and workbook version before changing a legacy array range.

Frequently Asked Questions

Why does Excel say #SPILL! when the cells look blank?
A cell may contain a space, a formula returning an empty string, or another value that is not obvious on the sheet. Select the cells in the outlined spill range and inspect the formula bar. Clear content only when it is safe to do so.

Can I type over one result in a spilled range?
No. The output cells are controlled by the anchor formula. Change the formula or its source data instead, or put a separate value outside the spill range. Typing into a spill-result cell may replace or interfere with the array output.

Does formatting cause #SPILL!?
Ordinary formatting such as a fill color or border does not usually block a spill. Content in a destination cell, merged cells, or an unsuitable output location are more typical causes. Check the warning message and the intended spill range to identify the specific issue.

Why does my formula work outside a Table but not inside it?
Excel Tables use calculated-column behavior and do not allow a dynamic-array formula to spill within the table body. Place the formula in a normal range outside the Table and leave room for the returned array. It can still use Table columns as its input.

How do I stop a formula from spilling?
If you only want one value, revise the formula so it returns a single result, or use an appropriate single-value calculation. In some contexts Excel may apply implicit intersection with the @ operator, but adding it is not a general fix: it changes which value is returned and may not match your intended result.

In short, #SPILL! usually signals that Excel knows a formula needs to return an array but cannot place that array in its destination. The dashed outline and error details help locate the cause; clearing a genuine obstruction, moving the formula, or adjusting the output range addresses most cases.

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.