formulastreak

#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.

Orders and price list · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Monstera
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042MonsteraPL-101Fern$9.5040
32002PT-2101Terracotta potPL-104Monstera$34.0012
42003TL-3013Watering canPL-107Snake plant$22.0025
52004PL-1011FernPT-210Terracotta pot$12.5060
62005SL-4014Potting mixPT-212Ceramic pot$19.0018
72006PL-1041MonsteraTL-301Watering can$18.009
82007PT-2122Ceramic potSL-401Potting mix$7.2580
92008PL-9991#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.

Leaf & Loam orders · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = #VALUE!
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusTotal
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped#VALUE!
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped#VALUE!
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped#VALUE!
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending#VALUE!
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped#VALUE!
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned#VALUE!
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped#VALUE!
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending#VALUE!
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped#VALUE!
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped#VALUE!
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped#VALUE!
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending#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.

Marketing campaigns · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = 3.0%
ABCDE
1CampaignClicksSalesConversionOwner
2Spring email1200363.0%Mara
3Instagram860121.4%
4Display ads00#DIV/0!Jonas
5Search ads2300693.0%Priya
6Newsletter540275.0%
7Partner blog00#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.

Orders and price list · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = #REF!
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042#REF!PL-101Fern$9.5040
32002PT-2101#REF!PL-104Monstera$34.0012
42003TL-3013#REF!PL-107Snake plant$22.0025
52004PL-1011#REF!PT-210Terracotta pot$12.5060
62005SL-4014#REF!PT-212Ceramic pot$19.0018
72006PL-1041#REF!TL-301Watering can$18.009
82007PT-2122#REF!SL-401Potting mix$7.2580
92008PL-1071#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.

March household budget · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = #NAME?
ABCDE
1CategoryTypeBudgetActualStatus
2RentFixed$1,200.00$1,200.00#NAME?
3GroceriesVariable$450.00$512.40#NAME?
4UtilitiesFixed$160.00$148.75#NAME?
5TransportVariable$120.00$96.50#NAME?
6Phone & internetFixed$65.00$65.00#NAME?
7Eating outVariable$150.00$203.10#NAME?
8SubscriptionsFixed$38.00$44.97#NAME?
9GymFixed$40.00$40.00#NAME?
10GiftsVariable$60.00$25.00#NAME?
11SavingsFixed$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.

Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = #NAME?
ABCDEFGH
1RepRegionJulAugSepTargetQ3 totalHit target?
2Amara OseiNorth$12,400$13,100$11,800$36,000$37,300#NAME?
3Ben FischerSouth$9,800$10,250$12,100$30,000$32,150#NAME?
4Lina ChenEast$15,200$14,800$16,900$45,000$46,900#NAME?
5Diego AlvarezWest$8,700$9,100$8,400$28,000$26,200#NAME?
6Eva NovakNorth$11,200$12,600$13,900$36,000$37,700#NAME?
7Farid HaddadSouth$13,300$12,900$14,100$39,000$40,300#NAME?
8Grace KimEast$10,100$9,600$10,800$33,000$30,500#NAME?
9Hugo MartinWest$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.

Practise it now