A dynamic-array formula such as FILTER, UNIQUE, SORT, or SEQUENCE is meant to return more than one result. When Excel cannot place those results on the sheet, it shows #SPILL! instead.
The fix is usually simple: identify the intended output area, then remove or move whatever is in its way. This guide covers the most common #SPILL! errors in Excel: blocked cells, Excel Tables, merged cells, and formulas whose output size cannot be determined.
Quick Fix for #SPILL! Errors in Excel
- Select the cell showing
#SPILL!. - Select the warning icon beside the cell.
- Choose Select Obstructing Cells, if Excel offers it.
- Clear the contents of the highlighted cells, or move the dynamic-array formula to a blank area.
- If the formula is inside an Excel Table, move it outside the Table.
- Unmerge any cells in the intended output area.
Excel normally shows a dashed blue border around the range where it wants to place the results. That border is your fastest diagnostic tool. Do not overwrite the cells inside it until you know whether they contain data you need.
Why a Formula Spills
Traditional formulas usually return one value in one cell. Dynamic-array formulas can return a list or grid from one formula cell, called the anchor cell. Excel fills the surrounding cells automatically; this is called spilling.
For example, enter this formula in G2:
=SORT(UNIQUE(B2:B9))Excel returns each distinct value from B2:B9, sorted alphabetically, beginning in G2. You should not copy that formula down. The results below the anchor cell belong to the same formula.
| Cell | Product |
|---|---|
| B2 | Paper |
| B3 | Pens |
| B4 | Paper |
| B5 | Folders |
| B6 | Pens |
| B7 | Labels |
| B8 | Folders |
| B9 | Paper |
Expected results in G2:G5: Folders, Labels, Paper, Pens. If G3 already contains a note such as “Review,” the formula in G2 returns #SPILL!.
For a practical workflow using these functions together, see this guide to a UNIQUE, FILTER, and SORT reporting pipeline.
1. Clear or Move Blocked Cells
This is the most common cause. A spill range must be completely available. A number, text value, space, formula, or even a formula that displays an empty string can block it.
Find the blocker safely
- Select the error cell, such as
G2. - Read the message in the error menu. It commonly states that the spill range is not blank.
- Choose Select Obstructing Cells.
- Review the selected cells. Move important notes or values elsewhere, then use Home > Clear > Clear Contents where appropriate.
Pressing Delete clears cell contents but retains formatting, which is normally what you want. Do not delete an entire row or column merely to solve a spill error unless that is genuinely part of your worksheet design.
A cell can look empty while still containing a formula such as =IF(A2="","",A2). Select the cell and check the formula bar before deciding it is safe to clear.
2. Move the Formula Outside an Excel Table
Dynamic-array formulas cannot spill within an Excel Table. If you enter a formula such as =UNIQUE(TableSales[Region]) in a Table column, Excel returns #SPILL! because Tables expect a row-by-row calculated column, not a variable-size output.
Recommended: keep source data in a Table, but place the report formula in a normal worksheet range beside or below it.
For example, if your source Table is named TableSales, enter this in a blank regular cell such as H2:
=SORT(UNIQUE(TableSales[Region]))The Table reference is still useful: it expands when you add new sales rows. The output simply needs room to spill outside the Table. This is often the cleanest way to combine dynamic reporting with Excel Tables that automatically expand.
If you must keep the formula in a Table, use a formula designed to return one result per row instead. A spill formula is not the right structure for that location.
3. Unmerge Cells in the Spill Range
Merged cells prevent Excel from writing a dynamic array across the affected area. This often occurs in report layouts with merged headings or presentation-style spacing.
- Select the dashed spill range, including any merged area.
- On the Home tab, select Merge & Center to unmerge the cells.
- Re-enter or recalculate the formula.
For report titles, use Center Across Selection instead of merging where possible. It gives a similar visual result without creating a structural obstacle for formulas.
4. Fix an Unknown or Indeterminate Spill Size
Excel must know how large the result will be before it can return a dynamic array. Some formulas use volatile functions that can keep changing the requested size, leaving Excel unable to establish a stable spill range.
For example, this formula can cause an indeterminate-size error:
=SEQUENCE(RANDBETWEEN(1,1000))RANDBETWEEN may produce a different row count whenever Excel recalculates. Instead, place the required count in a cell. Enter a fixed number, such as 25, in B1, then use:
=SEQUENCE(B1)Now Excel has a defined output size. If the changing count is intentional, calculate it once, paste the value, or redesign the process so the spill range does not depend on a volatile size.
Other #SPILL! Checks Worth Making
| Symptom | Likely cause | Solution |
|---|---|---|
| Message says spill range is not blank | Values, formulas, or spaces occupy the output area | Use Select Obstructing Cells; clear or move the contents. |
| Formula is in a Table | Tables do not support spilled array results | Move the anchor formula to a regular range. |
| Output crosses a merged cell | Merged cells cannot accept individual spill results | Unmerge the cells or move the formula. |
| Formula uses a changing row count | Excel cannot determine a stable output size | Replace the volatile size argument with a fixed cell reference. |
| Spill reaches the worksheet edge | There are not enough rows or columns available | Move the formula upward or left, or reduce the source range. |
Microsoft’s official #SPILL! error guidance also covers worksheet-edge and other less common causes.
Use AI to Explain the Formula, Then Verify in Excel
Copilot or ChatGPT can be useful when you inherit a workbook and do not recognize a long formula. Copy the formula and ask what range it is expected to return, which references determine its height and width, and whether it contains volatile functions.
Then verify the answer in Excel. Select the error cell, inspect the dashed spill border, and use the error menu to identify the actual obstruction. AI can suggest likely causes; Excel’s diagnostics are the source of truth for the current sheet.
If the formula still does not update after the spill issue is resolved, work through these Excel formula calculation checks before rebuilding the report.
Final #SPILL! Checklist
- Check the dashed border around the intended spill range.
- Clear or relocate values and formulas inside that range.
- Move dynamic-array formulas out of Excel Tables.
- Unmerge cells in the output area.
- Replace volatile size arguments with a stable cell reference where possible.
- Leave enough worksheet rows and columns for the result.
FAQ
Can I type into one cell of a spilled range?
No. Only the anchor cell contains the formula. Typing in any result cell blocks the array and causes a #SPILL! error. Change the source data or edit the anchor formula instead.
Why does the formula work outside a Table but not inside it?
A dynamic array needs multiple cells for one formula result. Excel Tables use a structured row-and-column layout and do not allow spilled array results within the Table.
Should I use IFERROR to hide a #SPILL! error?
Usually no. IFERROR can hide the warning while leaving the blocked report unresolved. Fix the obstruction first, then use error handling only when a blank or message is an intentional business rule.
Pingback: Excel @ Formula: Why It Appears and How to Fix Single-Value Results