Excel #SPILL! error
A dynamic array formula (UNIQUE, FILTER, SORT…) needs several free cells and one of them is taken.
Why it appears
- There is a value, even a space, in the area the result would spill into.
- The formula sits inside an Excel table, where spilling is not allowed.
- You used a whole column (A:A) and the result does not fit on the sheet.
How to fix it
- Select the error cell: Excel outlines the area it needs. Clear it.
- Move the formula outside the table or somewhere with free space.
- Use ranges the size of your data instead of whole columns.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Summary with the # operator
E2# means "everything spilling from E2". If the UNIQUE list grows, formulas using E2# grow with it. With SUMIF you build a pivot-style summary that updates itself.
In E2, list the regions without repeats, in alphabetical order.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Salesperson | Region | Amount | Region | Total | ||
| 2 | Carmen | North | 18,500.00 | ||||
| 3 | Jorge | South | 4,200.00 | ||||
| 4 | Lucía | North | 3,900.00 | ||||
| 5 | Andrés | Central | 21,000.00 | ||||
| 6 | Paola | South | 17,800.00 | ||||
| 7 | Ricardo | North | 6,400.00 | ||||
| 8 | Elena | Central | 5,900.00 | ||||
| 9 | Mateo | North | 19,900.00 | ||||
| 10 | Sofía | West | 800.00 | ||||
| 11 | Diego | Central | 4,600.00 | ||||
| 12 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What does #SPILL! mean in Excel?
- A dynamic array formula (UNIQUE, FILTER, SORT…) needs several free cells and one of them is taken.
- How do I get rid of the #SPILL! error?
- Select the error cell: Excel outlines the area it needs. Clear it. Move the formula outside the table or somewhere with free space. Use ranges the size of your data instead of whole columns.
- What is #SPILL! called in Spanish Excel?
- Spanish Excel shows it as #¡DESBORDAMIENTO!.