How to fix the #SPILL! error in Excel
Formula (Excel and Google Sheets)
=UNIQUE(B2:B8)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!.
- Result on the example
- a 4-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=UNIQUE(B2:B8)Result{4 rows × 1 col} "East", "West", "North", "South"
B2:B87 rows × 1 col: "East", "West", "East", "North", …A range: 7 cells in one column.
UNIQUE(B2:B8){4 rows × 1 col} "East", "West", "North", "South"UNIQUE: Distinct rows of a range. Spills.- array
B2:B87 rows × 1 col: "East", "West", "East", "North", …
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(B2:B8). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORT(UNIQUE(B2:B8)) | spills 4 × 1Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix the #SPILL! error
E2, list each region once. The cells below E2 are empty, so the list can spill.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.