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

The spill error Excel shows as #SPILL! means a formula has several results but nowhere to put them, usually because a cell in the way isn't empty. The fix is almost always to clear that cell or move the formula. This guide is for anyone using UNIQUE, FILTER, SORT or other newer functions who has met #SPILL! and wants to fix it quickly, at work or in an Excel test.

What the spill error Excel shows actually means#

Some formulas return more than one value. In Excel 2021, Excel 2024, Microsoft 365 and Excel for the web, those values spill: they fill the cells below and to the right of the formula. The area they fill is the spill range, and Excel draws a thin blue border around it when you select the formula.

Functions that spill include UNIQUE, FILTER, SORT, SORTBY, SEQUENCE, TEXTSPLIT, and XLOOKUP when it returns several columns. A plain range calculation like =D2:D9*2 spills too.

If anything stops the results from landing, the formula cell shows #SPILL! instead. The data is fine; only the space is the problem. To see the reason, select the cell with the error and click the small warning icon next to it. The first line of the menu names the cause, such as "Spill range isn't blank".

Cause 1: a cell in the spill range isn't empty#

This is the most common cause by far. Here is an order list with a note someone typed in F4:

With the regions in C2:C9, =UNIQUE(C2:C9) needs four cells, F2:F5, for East, West, South and North. F4 isn't empty, so F2 shows #SPILL!. Excel never types over your data.

To fix it:

1. Select the cell with #SPILL!, click the warning icon and choose Select Obstructing Cells. Excel selects F4.
2. Either delete the blocking content (press Delete), or move it somewhere else.
3. Or leave the note and put the formula in an empty column, such as H2. The four regions then spill into H2:H5.

The result appears as soon as the space is free. You don't need to retype the formula.

Blockers you can't see#

A cell can look empty and still block a spill. Check for:

• A single space, often left after someone "cleared" a cell with the space bar.
• A formula that returns empty text, such as =IF(A2="","",A2).
• White text on a white background.

Select the cell and look at the formula bar. If anything is there, press Delete.

Spills that block each other#

Two spilling formulas can't share cells. In the modern Excel functions lesson, =TEXTSPLIT(A2," ") in E2 splits a full name and spills the last name into F2. If you fill that formula down E2:E9 only, every row works. If you fill E2:F9, the formulas in column F block the spills from column E, and you get #SPILL! everywhere. Fill only the column where each formula starts.

Cause 2: merged cells in the spill range#

A spill can't land in merged cells. If the area below your formula includes a merged title or a merged block, the formula shows #SPILL!, and the warning menu says the spill range has a merged cell.

Select the merged cells and click Home > Merge & Center to unmerge them, or move the formula to an area with no merged cells. For a centred look without merging, use Center Across Selection in the cell format alignment settings.

Cause 3: the formula is inside an Excel table#

Excel tables (made with Insert > Table, or Ctrl+T on Windows) don't support spilled results. A FILTER or UNIQUE formula typed into a table column shows #SPILL! with the reason "Spill range in table".

You have two options:

• Move the formula outside the table. It can still refer to the table's data.
• Convert the table to a normal range: click inside it, then Table Design > Convert to Range. You lose table features such as automatic formatting and growing ranges.

Cause 4: the result is too big for the sheet#

A spill can't go past the last row or column of the worksheet. This usually happens with whole-column references. =A:A*2 needs 1,048,576 rows, every row in the sheet, so it only fits if it starts in row 1. Typed anywhere lower, it shows #SPILL!, because the result would run past the bottom edge of the sheet.

Fixes, from best to simplest:

• Use an exact range, such as =A2:A100*2, which needs only 99 rows.
• Work row by row: put =A2*2 in the first cell and fill it down with Ctrl+D (⌘D on a Mac).
• Use the @ operator to get one value instead of many: =@A:A*2 uses only the value from the formula's own row, the way older Excel did.

Less common reasons are a result whose size Excel can't work out in advance, for example when it depends on random functions, and a result so large that Excel runs out of memory. For those, use a smaller or fixed-size range.

How to work with spills and avoid #SPILL!#

A little planning prevents most spill errors.

• Leave room. Put spilling formulas in columns with nothing below them, or to the right of your data.
• Type headers above, not below. A label typed under a spill blocks it as soon as the list grows.
• Point at a whole spill with #. H2# means "everything H2 spilled". =ROWS(H2#) counts it and grows as the list grows, unlike a fixed range such as H2:H5.
• Build one spill from another. =SORT(H2#) sorts the spilled list without retyping the UNIQUE formula.

Our guides to the UNIQUE function, the FILTER function and SORT and SORTBY show more spilling formulas in action, and the FILTER function guide is a quick reference. If you need to share the file with someone on Excel 2019 or older, they won't see spills at all; for a one-time list without duplicates, Remove Duplicates works in every version.

Practise it#

Try the exercise on this page: the note in F4 blocks column F, so put UNIQUE where it has room. Then practise UNIQUE in a live spreadsheet, where a spill lands safely in an empty column, and work through the modern Excel functions lesson to use spill references like K2#.