Excel #SPILL! Error: How to Find and Fix It

To get rid of a #SPILL! error in Excel for Microsoft 365 on Windows, select the error cell, read the message beside it, and remove or correct whatever is preventing the formula’s results from filling the cells it needs. Most often, another value, formula, or merged cell is in the spill range; clear the obstruction or move the formula to an open area.

What the #SPILL! error means

Excel’s dynamic array formulas can return several results from one formula cell. That cell is the anchor; the other results appear in neighboring cells, forming a spill range. A #SPILL! error means Excel cannot place the complete result in that range. The formula may be valid, but its output cannot be displayed as intended.

For example, if A2:A5 contains Lima, Oslo, Accra, and Tokyo, a sorting formula entered in C2 can return the four names in order down column C. With those values in A2:A5 and the formula entered in C2, =SORT(A2:A5) returns Accra, Lima, Oslo, and Tokyo in C2:C5. SORT is available in Microsoft 365 and Excel 2021 or later. If a cell in C3:C5 is occupied, the formula cannot show its complete result and may display #SPILL! instead.

Spilled results are not separate formulas that you need to copy down. Excel calculates the array from the anchor and places the results automatically. The spill range can change size as the source data or formula changes, so a location that worked earlier can become blocked later.

Find the cause before changing the formula

Click the cell displaying #SPILL!. Excel typically shows an error indicator and a short explanation, such as “Spill range isn’t blank.” The message identifies the type of problem, which is more useful than guessing at the formula. In Windows Excel, select the warning icon beside the cell to see the explanation and, when available, choose Select Obstructing Cells to locate a blockage.

If the message does not identify a single blocking cell, inspect the area where the result is expected to appear. A formula can spill down, across, or into a larger two-dimensional range. Look at the full proposed spill area, not just the cell immediately beside the formula. You can also select the anchor cell and look for a highlighted outline showing the intended spill range.

Before clearing anything, check whether the cell contains information you need. An obstruction can be a value, a formula, or even a space entered in a cell. Formatting by itself—such as a fill color or border—does not normally block a spill. Hidden rows or columns can still contain cells that matter, so unhide them or inspect the range if the obstruction is not obvious.

Clear or move cells blocking the spill range

If the error message says the spill range is not blank, remove the contents from the cells Excel identifies or move the dynamic-array formula to a larger empty area. In Windows, you can select the obstructing cells and press Delete to clear their contents. This is different from deleting the cells themselves: pressing Delete keeps the worksheet layout in place while removing cell contents.

Use care when clearing a range. A cell may contain a formula or useful value that is not obvious because its displayed result looks blank. A formula that returns an empty string still occupies its cell and can block a spill. If you need the existing data, move it elsewhere or relocate the anchor formula instead of deleting it. Then check that the intended spill area is clear and recalculate if needed.

In the sorting example, if C4 contains an old note, the four-result output from C2 cannot fill C2:C5. Move or remove the note, or place the formula somewhere with four open cells below it. If the formula’s output grows when new source entries are added, leave additional room rather than clearing only the cells needed for today’s results.

On Mac, the same principle applies: identify the cells in the spill range and clear their contents or move the formula. Keyboard shortcuts differ: Delete clears selected cell contents in Windows, while on many Mac keyboards the equivalent is the Delete or Backspace key; available key labels can vary by keyboard. Excel for the web also supports dynamic arrays, though the placement and appearance of error menus can differ slightly from the desktop app.

Check for merged cells and worksheet boundaries

A merged cell in the spill range can prevent Excel from placing an array. This can happen even if the merged area looks empty. If the error message refers to merged cells, select the affected area and use Home > Merge & Center to review or unmerge it. The exact control may display a dropdown; choose the unmerge option. Unmerging can change the layout, so check the worksheet design and preserve any content before making the change.

A spill can also fail when its result would extend beyond the worksheet’s edge. For instance, a formula in the last few rows cannot spill a result that needs more rows than remain on the sheet. Move the anchor higher up or farther from the relevant boundary, or reduce the result size. The same logic applies to a result that needs more columns than are available to its right.

These cases are easy to overlook because there may not be an ordinary value to delete. If clearing visible contents does not fix the error, check for merged cells and consider whether the formula’s output would run off the edge of the worksheet.

Dynamic array formulas and Excel tables

Dynamic array formulas do not spill from inside an Excel table. If you enter a formula that needs to return multiple cells in a table column, Excel may show #SPILL! because table columns are designed to manage formulas as part of the table’s structured rows. Put the dynamic-array formula in a normal worksheet range outside the table, leaving enough clear space for its output.

You can still use table data as the formula’s source. For example, a formula outside a table can refer to table columns and return matching results into the worksheet grid. Keep the output range separate from the table and from other occupied cells. If you specifically need a formula calculated once per table row, use a suitable row-by-row formula rather than expecting one array formula to spill through the table.

This is not the same as saying that tables cannot contain formulas. Tables support calculated columns and ordinary formulas; the limitation is about a dynamic array trying to spill its results within the table itself. Moving the formula outside the table is often the simplest fix.

When the formula’s result size changes

Some spill problems arise because Excel cannot determine a stable result size. A formula whose array dimensions change during recalculation—for example, because a volatile random value controls how many rows it returns—can produce an indeterminate-size spill error. If the formula uses random or frequently changing inputs to set the number of results, make the size stable or use a separate input cell that changes only when you choose.

Very large results can also be impractical. A formula that attempts to return an enormous array may run into worksheet or calculation limits. Narrow the source range, filter the data before returning it, or put the result on a sheet with sufficient room. Avoid referencing entire columns in array calculations when only a small, known portion of the column contains relevant data; bounded ranges can reduce unnecessary calculation.

Do not assume every #SPILL! error means the function name is wrong. A misspelled or unavailable function usually produces a different error, such as #NAME?. Check the message attached to #SPILL! first, then review the formula only if the spill range, worksheet layout, and output size appear reasonable.

Keep spilled results working as data changes

Once the error is fixed, consider what will happen when the source list grows. Leave blank rows or columns around the output, avoid placing notes directly below a formula that spills downward, and keep results away from merged headings. If the output is intended for a report, reserve a clear area for it or place the formula on a dedicated sheet.

With source values in A2:A6 and status labels in B2:B6, a formula entered outside any table can return rows marked Open: =FILTER(A2:B6,B2:B6="Open","No matches"). If two records in B2:B6 are marked Open, the formula returns those two corresponding rows; if none are marked Open, it returns “No matches.” FILTER is available in Microsoft 365 and Excel 2021 or later. Leave at least as much open space as the largest likely result requires. If a later edit adds more Open records, Excel needs more cells for the expanded output.

When sharing a workbook, remember that recipients need a version of Excel that supports the dynamic-array function used. Microsoft 365 and Excel 2021 or later include functions such as SORT and FILTER; older Excel versions may not calculate them in the same way. If compatibility matters, consider whether a conventional formula, a table-based approach, or a copied static result is more appropriate.

Frequently Asked Questions

Can I just delete the cell showing #SPILL!?
Deleting the anchor formula removes the error, but it also removes the calculation. To keep the result, fix the cause described by Excel’s error message or move the formula to an unobstructed area.

Why does Excel show #SPILL! when the cells look empty?
A cell may contain a space or a formula that returns an empty-looking result. Hidden rows and columns can also conceal contents. Use the error menu’s Select Obstructing Cells option when available, and inspect the full proposed spill range.

Does formatting cause a #SPILL! error?
Ordinary formatting such as colors, borders, and number formats does not normally block a spill. Cell contents, merged cells, table placement, worksheet edges, or an unstable or excessively large result are more likely causes.

Can a dynamic array formula spill inside an Excel table?
No. Dynamic-array results cannot spill within the table itself. Enter the formula in a regular worksheet range outside the table, and leave open cells for the returned results.

How do I stop a spill range from overwriting my data?
Excel does not silently overwrite occupied cells; it reports an error when the required spill range is blocked. Keep output areas separate from notes and other data, and check where a formula may expand before entering it.

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.