Excel #SPILL Error - Causes and Fixes

The #SPILL! error means Excel has a formula that wants to return more than one cell, but Excel cannot place the full result on the worksheet.

This happens most often with dynamic array formulas such as FILTER, SORT, UNIQUE, SEQUENCE, and XLOOKUP formulas that return multiple columns. The formula may be correct; the worksheet simply does not have a clear spill range.

1. Quick Diagnosis Table

Start with the visible symptom, then use the matching fix.

If you are new to spilled formulas, review Excel dynamic array formulas first. The main idea is that one formula can return a whole range.

2. What a Spill Range Means

A spill range is the group of cells Excel needs to fill with the formula result. Only the top-left cell contains the formula. The neighboring cells show the formula output.

Example:

=UNIQUE(A2:A20)

If the formula finds six unique values, Excel needs six cells. If anything is already in one of those cells, Excel returns #SPILL! instead of overwriting your worksheet.

When a spill works, you can use F2# to refer to the full result in another formula:

=COUNTA(F2#)

3. Fix Blocked Spill Ranges

The most common cause is a hidden value, old formula, space character, or note in the spill area.

Use this checklist:

1. Select the cell showing #SPILL!.
2. Click the warning icon if Excel shows one.
3. Choose the option that identifies the obstructing cell.
4. Clear the blocking cells, or move the formula to a blank area.

Do not only check the first visible row. A spill can be blocked far below or to the right of the formula.

Example:

=FILTER(A2:D100,D2:D100="Open","No open items")

This formula may return 3 rows today and 30 rows next week. Leave extra room below the formula so the result can grow.

4. Fix #SPILL from Merged Cells

Dynamic arrays cannot spill through merged cells. Even a merged cell that looks empty can block the result.

If the merged cells are part of a report layout, first decide whether they are really needed. The guide to merge cells without losing data shows safer ways to combine text before formatting a header.

5. Fix #SPILL Inside Excel Tables

Excel Tables are great for source data, but spilled array formulas should usually sit outside the table body.

This formula is fine in a normal worksheet cell:

=SORT(UNIQUE(Table1[Region]))

If you place it inside a table column, Excel may show #SPILL! because each table row expects a single result.

Use this pattern instead:

For filtered report blocks, the Excel FILTER function tutorial shows how to place the formula where the result has room to expand.

6. Fix Oversized Spill Formulas

Sometimes the formula is technically valid, but the result is too large or too open-ended.

This can happen when a dynamic array formula points at a full column:

=FILTER(A:D,D:D="Open")

Use a bounded range or an Excel Table:

=FILTER(A2:D500,D2:D500="Open","No open items")

If your workbook supports newer trim references or the TRIMRANGE function, you can also reduce blank edges before feeding a range into a dynamic array. See TRIMRANGE and trim references for the preview-feature details.

7. Fix #SPILL from XLOOKUP Returning Multiple Columns

XLOOKUP can return more than one column when the return array has several columns:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$E$100,"Not found")

That formula needs three output cells across. If cells to the right are not empty, Excel returns #SPILL!.

For a broader XLOOKUP troubleshooting checklist, see XLOOKUP not working in Excel.

8. Fix #SPILL from FILTER

FILTER returns every row that matches the condition. If the number of matches changes, the spill range changes too.

Example:

=FILTER(A2:E100,B2:B100=H2,"No matching rows")

Before assuming the formula is wrong, check:

If the formula returns a #CALC! error instead, add the third argument: "No matching rows".

9. Prevent #SPILL Errors in New Workbooks

Use these habits when building dynamic array formulas:

• Put source data in Excel Tables, but place spill formulas outside the table
• Keep a blank output area below and to the right of the formula
• Avoid merged cells where dynamic reports need to expand
• Use bounded ranges or trimmed ranges instead of full-column references
• Use A2# references when another formula needs the whole spilled result
• Test formulas with a small range before connecting them to a large dataset

These habits matter most in dashboards and recurring reports, where today's small list can become next month's large list.

10. FAQ

Why does Excel say spill range is not blank when it looks empty?

The blocking cell may contain a space, old formula, hidden character, or merged cell. Select the #SPILL! warning and let Excel point to the obstructing cell, then clear that cell or move the formula.

Can I turn off spilling?

No. Spilling is how modern dynamic array formulas return multiple results. If you only need one value, use a formula that returns one cell, or wrap the result with a function such as INDEX.

Why does #SPILL happen in an Excel Table?

Excel Table columns are designed for one result per row. Place the dynamic array formula outside the table and refer to the table columns from there.

Is #SPILL a formula syntax error?

Usually no. The formula may be valid. #SPILL! often means the worksheet layout is blocking the result.

11. Related Guides

• Learn the core spill behavior in Excel dynamic array formulas.
• Build filtered reports with the Excel FILTER function tutorial.
• Choose between spilled reports and one-value lookups with Excel FILTER vs VLOOKUP.
• Review broader error patterns in Excel formula errors.
• Fix lookup-specific spill problems with XLOOKUP not working in Excel.
• Manage blank range edges with TRIMRANGE and trim references.
• Avoid layout blockers with merge cells without losing data.