An error in a cell isn’t a broken spreadsheet — it’s Excel telling you precisely what went wrong, in a code you can learn to read. Each error type points at a specific cause, so the green triangle or the # message is a diagnosis, not a dead end. This decodes the errors you’ll actually hit, what each one means, and the fastest fix for each — plus the tools that trace an error back to its source so you’re not guessing.
The error codes at a glance
Most errors fall into a handful of types, and the code itself tells you the category of problem.
| Error | What it means |
|---|---|
| ##### | Not an error — the column is just too narrow to show the value |
| #REF! | A cell the formula referenced was deleted |
| #VALUE! | Wrong type of data — text where a number was expected |
| #N/A | A lookup found no match |
| #DIV/0! | Dividing by zero or an empty cell |
| #NAME? | Excel doesn’t recognize a name or function |
| #SPILL! | A dynamic-array formula has no room to spill |
The first one isn’t even a real error: ##### just means the column is too narrow, fixed by widening it. The rest are genuine formula problems, but each names its own cause once you know the vocabulary. Read the code first, and you’ve already narrowed the diagnosis to one category before looking at the formula.
#REF! and #NAME? — you broke a reference
These two mean the formula is pointing at something that isn’t there. #REF! appears when you delete a row, column, or cell that a formula referenced — the reference has nowhere to point, so it collapses to an error.
| Error | Usual cause | Fix |
|---|---|---|
| #REF! | Deleted a referenced row, column, or cell | Rewrite the broken reference |
| #NAME? | Misspelled function or unknown name | Check spelling; quote any text |
The fix for #REF! is to rewrite the broken reference (the formula will literally show #REF! where the lost cell was), and the prevention is to be careful deleting rows or columns that formulas depend on. #NAME? means Excel doesn’t recognize something you typed — usually a misspelled function (=SUME(...)), a named range that doesn’t exist, or text that’s missing its quotation marks. Check the spelling of the function and make sure any text inside the formula is wrapped in quotes. Both errors are about names and references that don’t resolve.
#VALUE! — the wrong kind of data
#VALUE! is the generic “I can’t do math on this” error, almost always caused by a formula trying to calculate with text. =A1+B1 where B1 contains “N/A” or a stray space produces it.
Track down the offending cell — it’s holding text where the formula expects a number — and clean it. A frequent hidden culprit is numbers stored as text (often from an import), which look numeric but won’t calculate; converting them to real numbers resolves the error. The functions in your formula toolkit like SUM quietly ignore text, but arithmetic operators (+, -, *) don’t, which is why a SUM works where a + throws #VALUE!.
#N/A — the lookup found nothing
#N/A means “not available” — a lookup function searched and came up empty. It’s the most common error in any sheet that uses VLOOKUP or XLOOKUP.
=IFNA(VLOOKUP(E2, A:C, 3, FALSE), "Not found")Sometimes #N/A is expected (a new ID not yet in your table), and the clean fix is to wrap the lookup in IFNA to show a friendly message instead. But often it signals a real mismatch — a trailing space, a number stored as text, or an exact-match lookup that genuinely has no hit. Before suppressing it, confirm the value really should be found; the VLOOKUP guide covers the exact-match and data-type causes. Treat #N/A as a question first, then silence it once you’ve answered it. Note the choice of IFNA over IFERROR here: IFNA catches only #N/A, while IFERROR catches every error type. On a lookup, IFNA is the safer wrapper — it handles the “not found” case but still lets a #REF! or #VALUE! surface, so a different, genuine problem in the same formula doesn’t get hidden behind your “Not found” message.
#DIV/0! and #SPILL! — math and space
These two have very different causes. #DIV/0! means a division by zero or an empty cell — common in formulas like =sales/units when units is blank.
=IFERROR(A2/B2, 0) 'show 0 instead of #DIV/0! when B2 is emptyWrap the division in IFERROR to handle the empty-denominator case gracefully, as Microsoft’s guide to the #DIV/0! error describes. #SPILL! is newer: a dynamic-array formula wants to fill several cells but something is blocking the range. The fix is almost always to clear the cells below and to the right of the formula so it has room to spill — click the error’s dropdown and “Select Obstructing Cells” to find what’s in the way.
The rarer ones: #NUM! and #NULL!
Two you’ll hit less often but should still recognize. #NUM! means a calculation produced an invalid or impossible number, and #NULL! means a range was written wrong.
| Error | Cause |
|---|---|
| #NUM! | An impossible result — square root of a negative, or a number too large to represent |
| #NULL! | A space used between ranges instead of a comma, so the ranges don’t intersect |
#NUM! usually points at the math itself — a function fed an input it can’t handle, like =SQRT(-1), or an iterative calculation that won’t converge. Check the inputs are in the valid range for the function. #NULL! is almost always a typo: =SUM(A1:A5 C1:C5) with a space instead of a comma tells Excel to sum the intersection of two ranges that don’t overlap. Replace the space with a comma and it resolves. Both are rare enough that recognizing the code is half the battle.
Trace it, then prevent it
The instinct to wrap every formula in IFERROR is worth resisting: it hides the symptom without fixing the cause, and a genuinely broken formula silenced with IFERROR will quietly return wrong numbers. Use IFERROR for errors you expect and understand — an empty denominator, a lookup that legitimately misses — and trace the rest to their root. Read the code, follow the precedents, fix the actual problem, and only then suppress what remains. An error is information; the goal is to understand it, not just to make it disappear.