#N/A, #VALUE!, #REF! and other formula errors
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
#N/A means a lookup found nothing, #VALUE! means maths on the wrong type of value, #DIV/0! means dividing by zero or a blank, #REF! means a reference to a cell that doesn't exist, #NAME? means a misspelled function or text without quotes, and #NUM! means an impossible number. In Excel, #SPILL! means an array result is blocked and #CALC! a calculation problem such as an empty FILTER.
Errors aren't failures; they're the spreadsheet telling you exactly what went wrong. Each one has a short list of usual causes. Each example below shows the error live: fix the formula and watch it go away.
#N/A: not found
A lookup couldn't find its key. Check for typos, extra spaces (fix with TRIM), numbers stored as text, or a table range that slid when you filled the formula down. If “not found” is expected, handle it with IFNA or XLOOKUP's fallback.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | Monstera | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | Terracotta pot | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | Watering can | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | Fern | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | Potting mix | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | Monstera | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | Ceramic pot | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-999 | 1 | #N/A |
#VALUE!: wrong type of value
Maths on text that isn't a number. Here the formula multiplies a quantity by a product name instead of its price. Click a cell to see which arguments the formula uses.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Total |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | #VALUE! |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | #VALUE! |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | #VALUE! |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | #VALUE! |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | #VALUE! |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | #VALUE! |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | #VALUE! |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | #VALUE! |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | #VALUE! |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | #VALUE! |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | #VALUE! |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | #VALUE! |
#DIV/0!: dividing by zero
The divisor is 0 or an empty cell. Test it first with IF(B2=0, …), or catch it with IFERROR.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner |
| 2 | Spring email | 1200 | 36 | 3.0% | Mara |
| 3 | 860 | 12 | 1.4% | ||
| 4 | Display ads | 0 | 0 | #DIV/0! | Jonas |
| 5 | Search ads | 2300 | 69 | 3.0% | Priya |
| 6 | Newsletter | 540 | 27 | 5.0% | |
| 7 | Partner blog | 0 | 0 | #DIV/0! | Kenji |
#REF!: a reference that doesn't exist
The formula points at a cell that was deleted, or asks a lookup for a column outside its table. Here VLOOKUP asks for column 5 of a 4-column table.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | #REF! | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | #REF! | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | #REF! | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | #REF! | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | #REF! | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | #REF! | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | #REF! | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-107 | 1 | #REF! |
#NAME?: a name the spreadsheet doesn't know
A misspelled function (=SUMM(…)), text without quotes (Yes instead of "Yes"), or a function your version doesn't have, like XLOOKUP in Excel 2019.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Type | Budget | Actual | Status |
| 2 | Rent | Fixed | $1,200.00 | $1,200.00 | #NAME? |
| 3 | Groceries | Variable | $450.00 | $512.40 | #NAME? |
| 4 | Utilities | Fixed | $160.00 | $148.75 | #NAME? |
| 5 | Transport | Variable | $120.00 | $96.50 | #NAME? |
| 6 | Phone & internet | Fixed | $65.00 | $65.00 | #NAME? |
| 7 | Eating out | Variable | $150.00 | $203.10 | #NAME? |
| 8 | Subscriptions | Fixed | $38.00 | $44.97 | #NAME? |
| 9 | Gym | Fixed | $40.00 | $40.00 | #NAME? |
| 10 | Gifts | Variable | $60.00 | $25.00 | #NAME? |
| 11 | Savings | Fixed | $300.00 | $300.00 | #NAME? |
Check yourself
One real exercise from the “Decisions” unit. Checked on our server; no account needed.The formula in H2 shows #NAME? instead of Yes or No. Fix it.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Q3 total | Hit target? |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | $37,300 | #NAME? |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | $32,150 | #NAME? |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | $46,900 | #NAME? |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | $26,200 | #NAME? |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | $37,700 | #NAME? |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | $40,300 | #NAME? |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | $30,500 | #NAME? |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 | $45,400 | #NAME? |
Common mistakes
Hiding every error with IFERROR before finding out why it happened.
Fixing the first cell of a filled column but not refilling the rest.
Comparing numbers stored as text (left-aligned) with real numbers in lookups.
Questions
What does #SPILL! mean?+
In Excel 2021, Excel 2024 and Microsoft 365, a formula that returns several values needs empty cells to spill into. #SPILL! means it can't: something is in the way, the result would run off the sheet, or it's inside a Table or merged cells. Clear the cells it needs.
What does #CALC! mean?+
An Excel calculation error, for example FILTER finding no matches without an if_empty argument, or a LAMBDA that was never called.
How do I find which cell causes an error?+
Click the cell and read its formula; then click each referenced cell. Errors flow downstream, so the first error in the chain is the one to fix.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.