#N/A error in Excel: what it means and how to fix it
#N/A means a lookup didn't find what it was looking for. Here are the six usual causes, how to spot each one on the sheet, and the fix, with a practice sheet.
#SPILL! error in Excel: what it means and how to fix it
Works in Excel 365, Excel 2021 and later, and Excel for the webKey takeaways
- #SPILL! means the formula's result needs cells that aren't empty.
- Click the error: Excel outlines the spill range with a dashed border. Clear what's inside it.
- A spilling formula can't sit inside an Excel table. Move it next to the table.
- Whole-column references such as A:A can spill a million rows. Use the data's range instead.
On this page
#SPILL! means a formula returned several values but the cells it needs to place them in aren’t empty. Since Excel 365, functions like FILTER, UNIQUE and SORT return whole lists that “spill” into the cells below; if anything is in the way, the formula shows #SPILL! instead. Click the error and Excel draws a dashed border round the cells it needs: clear them and the result appears.
The usual cause: something in the way
UNIQUE in E2 needs three cells, E2 to E4, for the three regions. E4 holds the word “Notes”, so nothing spills:
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Rep | Region | Regions | ||
| 2 | Ana | East | #SPILL! | ||
| 3 | Ben | West | |||
| 4 | Cara | East | Notes | ||
| 5 | Dan | North |
Delete “Notes” (or move the formula) and the list appears. The dashed cells are the spill: you only typed the formula in E2.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Rep | Region | Regions | ||
| 2 | Ana | East | East | ||
| 3 | Ben | West | West | ||
| 4 | Cara | East | North | ||
| 5 | Dan | North |
Cells that look empty but aren’t
A cell with a single space, a formula returning "", or text formatted white all block a spill. Select the whole dashed range and press Delete.
Inside an Excel table
Spilling formulas don’t work inside a table (an Insert › Table range): the result would have to grow the table. Put the formula in a normal cell next to the table, or convert the table back to a range.
The result is too big
=UNIQUE(A:A) asks for a whole column: over a million rows. If the result could run off the sheet, Excel shows #SPILL!. Point at the data’s real range, such as A2:A500, or at a table column such as Sales[Region].
Merged cells
A spill can’t land in merged cells. Unmerge them, or move the formula.
FAQ
What does #SPILL! mean in Excel?
A formula returned several values, which Excel places in the cells below or beside it (the spill range), but at least one of those cells isn't empty, so nothing is shown.
Why do I get #SPILL! when the cells look empty?
They may hold a space, an empty string from another formula, or text in white. Select the spill range and press Delete to clear it.
Can I stop a formula from spilling?
Put @ before a function that returns a list, such as =@UNIQUE(B2:B6), to return only the first value, or pick one value with INDEX.
Keep going
Guides that build on this one.
#NAME? error in Excel: what it means and how to fix it
#NAME? means Excel doesn't recognise a word in your formula: a misspelt function, text without quotes, a missing colon. Find it and fix it, with a practice sheet.
#NUM! error in Excel: what it means and how to fix it
#NUM! means a calculation has no valid answer: an IRR with no rate to find, a square root of a negative. Each cause and its fix, with a practice sheet.
Reading is step one. Writing the formula is the skill.
Practise in a live grid that grades what your formula calculates, with a specific hint when it's wrong.
Runs in your browser · Graded on your result · No install