Excel Formula Errors: #N/A, #VALUE!, #REF! and How to Fix Them
What each Excel error means (#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #SPILL!) and the quickest fix, plus silent errors that show no warning.
By Ayoub Farid · · 3 min read
Every Excel error code tells you something specific. Once you know what each one means, fixing them takes seconds.
The error codes
| Error | Means | Usual fix |
|---|---|---|
#DIV/0! | Dividing by zero or an empty cell | Check the divisor; lock totals with $ |
#N/A | A lookup didn't find the value | Check spaces, text vs numbers, FALSE |
#VALUE! | Wrong type of data (text in maths) | Clean the data or use SUM instead of + |
#REF! | Reference to a deleted cell | Undo, or rewrite the reference |
#NAME? | Excel doesn't recognise a name | Typo in function, text without quotes |
#NUM! | Impossible number | Check inputs (e.g. DATEDIF start > end) |
#SPILL! | A spilled result is blocked | Clear the cells in the way |
#NULL! | Space instead of comma or colon | Use , or : between references |
#### | Column too narrow (or negative date) | Widen the column |
#DIV/0!: dividing by zero or a blank
Most often caused by filling down a formula that divides by a total without locking it. =B2/B14 becomes =B3/B15, which is empty. Fix: =B2/$B$14. Full guide: #DIV/0! error and absolute references.
#N/A: the lookup found nothing
Lookups that can't find a match. Common causes: trailing spaces, numbers stored as text, missing FALSE in VLOOKUP, range shifted when filled down. See VLOOKUP not working.
#VALUE!: text where a number should be
=B2+B3 where B3 contains "n/a" or a number stored as text with a space. SUM ignores text, + doesn't. Also caused by ranges of different sizes in SUMIFS.
#REF!: a deleted cell
You deleted a row, column or sheet that a formula used. The reference is gone, so Excel replaces it with #REF!. Undo (Ctrl+Z) if you can, otherwise rewrite the formula.
#NAME?: a name Excel doesn't know
- Misspelt function:
=SUMM(B2:B5) - Text without quotes:
=IF(A2=Paid,...) - A function your Excel version doesn't have (XLOOKUP in Excel 2019)
#SPILL!: something is in the way
In Excel 365, functions like FILTER and UNIQUE need empty cells to spill into. Clear what's in the way.
The dangerous ones: no error at all
The worst mistakes don't show an error:
- Range one row short:
=SUM(B2:B12)instead of B13. A plausible, wrong total. - Typed numbers:
=4200+3900won't update when data changes. - VLOOKUP without FALSE: returns a wrong row on unsorted data.
- Hidden IFERROR: wrapping everything in IFERROR hides real problems.
Check: change an input and see if results move; click the last filled cell and read its formula.
Tools that help
- Formulas > Trace Precedents shows which cells feed a formula.
- Formulas > Evaluate Formula steps through a calculation.
- Ctrl + ` (grave accent) shows all formulas instead of results.
Learn from your errors
Cellio's feedback is built around these mistakes. When an answer is wrong, it names the reason: the range is too short, a $ is missing, you divided by an empty cell, you typed a number, or the function name isn't recognised. That's the fastest way to stop making them.
Frequently asked questions
What does #VALUE! mean in Excel?
A formula is trying to do maths with text or the wrong kind of value.
How do I fix #REF! in Excel?
Undo the deletion that caused it, or rewrite the formula to point at existing cells.
How do I hide errors in Excel?
Use IFERROR or IFNA, but only where the error is expected. See IFERROR.
Why does my formula show the formula instead of the result?
The cell is formatted as Text, or Show Formulas (Ctrl+`) is on.