# How to fix the #SPILL! error in Excel

Clear the cells the result needs to spill into. =UNIQUE(B2:B8) needs E2:E5 empty; anything in the way, even a space, causes #SPILL!.

**Excel:** `=UNIQUE(B2:B8)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Order | Region | Amount |  | Regions |
| SO-1 | East | 420 |  |  |
| SO-2 | West | 380 |  |  |
| SO-3 | East | 150 |  |  |
| SO-4 | North | 275 |  |  |
| SO-5 | West | 610 |  |  |
| SO-6 | South | 95 |  |  |
| SO-7 | North | 330 |  |  |

Formula in E2: `=UNIQUE(B2:B8)`

Result (spills 4 × 1): East; West; North; South

## How it works

UNIQUE returns several values and spills them into the cells below the formula. If any of those cells holds something, even a space or a formula returning "", Excel won't overwrite it and shows #SPILL! in the formula cell instead. With `E3:E5` empty, the four regions spill normally.

## Other ways

- `=SORT(UNIQUE(B2:B8))`: Sorted A to Z. It needs the same empty cells. (East, North, South, West.)

## Common mistakes

- Click the #SPILL! cell: a dashed border shows the range it needs, and the warning menu offers Select Obstructing Cells.
- Merged cells in the spill range block it too. Unmerge them.
- Spilling formulas don't work inside an Excel Table. Put them next to the Table instead.

## Questions

**What does the same problem look like in Google Sheets?**

Google Sheets shows #REF! with the message "Array result was not expanded because it would overwrite data".

**Can I make the result smaller instead?**

Yes. TAKE limits the rows: `=TAKE(UNIQUE(B2:B8),3)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/spill-error-blocked-range