Why your Excel formula returns #VALUE! — and the 7 other errors decoded

Excel errors look scary because they're cryptic, but each one is just Excel's way of saying "I tried to do what you asked and ran into this specific problem." Eight errors cover 99% of cases. Here they are.

#VALUE!

What it means: the wrong type of data was passed to the formula. Usually a number function got text.

Common cause: a "looks like a number" cell that's actually stored as text (right-aligned, with a green triangle in the corner). =A1+B1 where one of them is "42" (text) instead of 42 (number) → #VALUE!.

Fix:

• Click the cell, then the warning triangle → Convert to Number.
• Or wrap with =VALUE(A1)+VALUE(B1).
• Or use functions that ignore text: =SUM(A1, B1) instead of =A1+B1.

#REF!

What it means: the formula points to a cell that no longer exists.

Common cause: you deleted a row or column that the formula referenced. Or you cut-and-pasted in a way that broke the link.

Fix: there's no automatic fix — you have to either undo the deletion (Ctrl + Z immediately) or rewrite the formula with a valid reference.

#DIV/0!

What it means: you tried to divide by zero (or by an empty cell, which Excel reads as 0).

Fix:

=IFERROR(A1/B1, 0)

or, cleaner:

=IF(B1=0, 0, A1/B1)

#NAME?

What it means: Excel doesn't recognize a name in the formula. Usually a typo: =SUMM(A1:A10) instead of =SUM(A1:A10).

Common causes:

• Misspelled function name.
• Text in the formula without quotes, e.g. =IF(A1=red, "yes", "no") — should be ="red".
• A defined name that doesn't exist in this workbook.

#N/A

What it means: "Not Available" — Excel looked for something and couldn't find it.

Common cause: a VLOOKUP/XLOOKUP/MATCH didn't find the value in the lookup range.

Fix:

=IFERROR(XLOOKUP(...), "Not found")

or, smarter, fix the underlying data — usually a trailing space, different case, or a missing entry in the lookup table.

#NUM!

What it means: a number-related problem. The most common: a math result that's outside Excel's range (>1E+308) or a function that needs a positive number but got a negative one.

Examples: =SQRT(-1) → #NUM! because square root of negative isn't a real number. =DATE(-1,1,1) → #NUM! because year -1 isn't valid.

#NULL!

What it means: you used a space between two ranges that don't actually intersect.

Cause: =SUM(A1:A10 B1:B10) — that space is the intersection operator. A1:A10 and B1:B10 don't share any cells, so Excel returns #NULL!.

Fix: replace the space with a comma: =SUM(A1:A10, B1:B10).

#SPILL!

What it means: a dynamic-array formula (UNIQUE, FILTER, XLOOKUP etc.) wants to fill multiple cells but something is blocking the destination range.

Common causes:

• A non-empty cell sits where the spill needs to land.
• The spill range hits the edge of the sheet.
• The spill range contains a merged cell.

Fix: click the #SPILL! cell, look at the dashed blue border showing where it wanted to expand, and clear those cells.

The catch-all wrapper

When you can't tell which error a formula might throw, wrap it:

=IFERROR(your_formula, "—")

or use IFNA(...) to specifically silence only #N/A while letting real errors surface.

TL;DR

#VALUE! = wrong type. #REF! = deleted reference. #DIV/0! = divide by zero. #NAME? = typo. #N/A = lookup didn't find it. #NUM! = math problem. #NULL! = bad intersection. #SPILL! = dynamic array blocked. Read the error first, then fix the cause — IFERROR only hides problems, it doesn't solve them.