Excel errors explained: #REF!, #DIV/0!, #N/A, #VALUE!

An Excel error value is a message, not random breakage. Each of the eight classics, #REF!, #DIV/0!, #N/A, #VALUE!, #NAME?, #NUM!, #NULL!, and #SPILL!, reports one specific failure: a deleted reference, a zero divisor, a lookup that found nothing, text where a number should be. Read the value and you know where to look. The fix is usually mechanical.

Find your error in the table, then jump to its section for the cause and the repair.

Which error do you have?

One imposter worth clearing up first: a cell full of # signs (#####) is not a formula error. Excel shows it when the column isn’t wide enough to display the cell’s contents, so the fix is to widen the column. The only formula case is a date or time calculation that comes out negative, which also displays as #####.

What does #REF! mean in Excel?

#REF! shows when a formula points at a cell that no longer exists, and the usual reason is blunt: the cells it referenced were deleted, or pasted over. The formula didn’t change. The sheet changed underneath it.

If you just deleted the rows or columns, press Ctrl+Z immediately and they come back, formula intact. The durable fix is to stop referencing cells individually:

Lookups have their own flavor of #REF!. Microsoft’s example: =VLOOKUP(A8,A2:D5,5,FALSE) fails because it asks for column 5 of a range that is only four columns wide. Widen the range or lower the column number. Better yet, use a lookup that doesn’t count columns by hand at all: XLOOKUP and INDEX/MATCH point at the return column directly, so this whole class of #REF! can’t happen.

What does #DIV/0! mean in Excel?

Excel shows #DIV/0! when a number is divided by zero. That includes the case people forget: a divisor cell that is blank divides like a zero.

Often the zero is honest: a region with no orders yet, a tally that found nothing to count. (If the denominator comes from a conditional count, the COUNTIF and SUMIF guide covers that side.) When a zero is a legitimate state rather than a bug, test the denominator instead of letting the error through:

If C3 is zero or empty, the formula returns 0 instead of the error; swap the 0 for "" to show nothing, or for text like "No orders". That IF test is the recommended pattern. The lazier alternative, wrapping the division in IFERROR, suppresses all errors, not just #DIV/0!. More on that trap in the ignore-or-hide section below.

What does #N/A mean in Excel?

#N/A generally means the formula can’t find what it was asked to look for, and its natural habitat is lookups: XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, and MATCH all return it when the lookup value isn’t found.

If SKU-1005 isn’t anywhere in A2:A40, that formula returns #N/A, and that’s the correct answer. The maddening case is when the value plainly is there and you still get #N/A: an approximate match doing the matching, a number stored as text on one side, a trailing space nobody can see. Those causes, and the fix for each, get their own walkthrough in why your VLOOKUP returns #N/A. If you’re on a current version of Excel, XLOOKUP also sidesteps several of them and takes a built-in argument for what to show when there’s no match.

One more thing that surprises people: #N/A is an error value people sometimes enter on purpose. Microsoft notes it’s common practice in chart data, because #N/A values won’t plot, whereas zeros would drag a line to the floor.

What does #VALUE! mean in Excel?

#VALUE! is Excel’s broadest complaint: either something is wrong with how the formula is typed, or something is wrong with the cells it references. In practice the second one dominates, and the classic case is arithmetic on text.

Here the culprit announces itself. The trickier version is a cell that looks blank or numeric but isn’t: a cell holding nothing but a space, or a hidden space that came along with pasted data. A quick =ISTEXT(C3) in a spare cell returns TRUE when a suspect cell is secretly text. The fix is always the same shape: make the referenced cell hold what the formula expects, a real number in place of TBD, no stray characters in place of an invisible space.

What about #NAME?, #NUM!, #NULL!, and #SPILL!?

The other four are quicker to dispatch.

#NAME? means Excel doesn’t recognize a name

The top reason is simply a typo in the function name:

Other ways this happens: text used without quotation marks, a missing colon in a range reference (B2B12 instead of B2:B12), a misspelled defined name, or a function whose add-in isn’t enabled. A function that doesn’t exist in the Excel version opening the file does it too, which is exactly what happens when a workbook using XLOOKUP lands in Excel 2016 or 2019 (see VLOOKUP vs XLOOKUP for who runs what), or when IFS is spelled with one F too many; the IF function guide covers the correct names and syntax.

One rule is absolute here, and it’s Microsoft’s own: #NAME? signals syntax that needs correcting, so fix it. “Do not use any error-handling functions such as IFERROR to mask the error.”

#NUM! means a number Excel can’t work with

#NUM! appears when a formula’s numbers themselves are the problem. Three ways to get there: typing a formatted value like $1,000 into an argument (enter 1000 instead; the dollar sign and comma mean other things inside formulas), an iterating function like IRR or RATE that can’t find a result, and a result too large or too small for Excel to show.

#NULL! means two ranges that don’t intersect

Nearly every #NULL! is a space that should have been a comma. To sum both ranges, write =SUM(A1:A10,C1:C10).

#SPILL! means the results have nowhere to land

Modern array formulas return several values at once and spill them into neighboring cells. #SPILL! is returned when Excel can’t place those results in the grid, and the first cause on Microsoft’s list is the one to check: the spill range isn’t blank, something is sitting in the cells the result needs. Select the error alert and choose Select Obstructing Cells, then clear or move whatever it finds. Two structural blockers to know about: spilled array formulas aren’t supported inside Excel tables, and they can’t spill into merged cells.

How do you find every error in an Excel workbook?

Errors rarely sit on the sheet you’re looking at. They hide on the assumptions tab, in column BQ, on the sheet nobody has scrolled since March. Excel gives you two built-in sweeps, and both are worth knowing.

Go To Special selects every error cell on a worksheet at once. Press Ctrl+G (or go to Home > Find & Select), choose Go To Special, then Formulas. The check boxes under Formulas narrow the type; leave only the errors box ticked and Excel highlights every error cell on the sheet in one shot. Handy for counting them, coloring them, or just seeing how bad it is.

Error Checking works through them one at a time instead, like a spell checker. It lives at Formulas > Formula Auditing > Error Checking, and it explains each error as it goes; the actions on offer differ by error type. Slower, but it tells you what each error is, not just where.

The catch with both: they operate on a worksheet, so a twelve-sheet workbook means twelve passes, and the sheets you skip are precisely the ones where errors survive.

Should you ignore or hide errors in Excel?

The question behind “how to ignore all errors in Excel” is usually about the green triangles, the little error indicator Excel puts in the top-left corner of a flagged cell. You can silence those: select the flagged cell, open the alert, and choose Ignore Error, which marks that error to be skipped in future checks. (Changed your mind? File > Options > Formulas > Reset Ignored Errors brings them all back. The same options page can turn background error checking off entirely.)

Be clear about what that does: it hides the flag, not the problem. The error value is still in the cell, still poisoning any formula that reads it. Cosmetic tricks like converting errors to 0 and blanking the zeros with a conditional format go a step further in the wrong direction: now there’s a real 0 in the cell, quietly counted by everything that touches it.

Wrapping the formula is the more honest route, if you wrap narrowly:

• A denominator that is legitimately zero sometimes: test it with =IF(C3, B3/C3, 0), as in the #DIV/0! section above.
• A lookup where no match is a valid outcome: wrap it in IFNA, which replaces only #N/A and lets every other error through. The trade-off between IFNA and IFERROR is worked through in the VLOOKUP #N/A guide.

What you should not do is blanket the sheet in IFERROR(..., 0). Microsoft’s warning on this is unusually direct: IFERROR and IF(ISERROR()) “are blanket error handlers, in that they will suppress all errors,” so a formula that later breaks for a completely different reason, a #REF! from a deleted column, a #NAME? from a typo, stops erroring and starts reporting zero. An error you can see is annoying. A broken formula that reports a confident number is expensive.

So the honest answer: ignore an error only after you’ve diagnosed it, wrap only the specific error you expect, and never mask a syntax error like #NAME? at all.

The errors you can see are the cheap ones

Every error value on this page is loud. It sits in the cell in capital letters with punctuation, someone eventually spots it, and it gets fixed. The failures that actually cost money are the quiet ones: the approximate match that returns the wrong row without complaint, the IFERROR(..., 0) that has been swallowing a broken reference since last quarter. The European Spreadsheet Risks Interest Group (EuSpRiG) keeps a public archive of what failures like these have cost real companies.

Fix causes, wrap narrowly, and sweep the whole workbook rather than the sheet in front of you. And when a workbook other people depend on changes, compare the two versions before you trust the new one: an error you catch on the way in never makes it to the board pack.