Excel errors: what #VALUE!, #N/A and #REF! mean

Excel errors are codes, and each code points to one kind of problem. #VALUE! means a calculation hit text, #REF! means a referenced cell is gone, and #N/A means a lookup found nothing. A code in a cell is Excel naming the part of the calculation that failed, so the code shows which cells to check first.

A search box filters the table of codes, so typing the code shown in the cell gives its meaning, usual cause and fix on one row. Once the formula is right, IFNA and IFERROR can hide the errors that are expected, such as a lookup for a value that has not been entered yet.

Types of Excel errors and what each code means

Nine error values, plus ##### and the circular reference warning, sit in one table, and each row gives the code's meaning, usual cause and fix. An error value is what Excel puts in a cell instead of a result when a formula cannot finish. Every error value starts with a hash sign, and each one names a different cause.

The table's 3 groups (11 rows)

Common error values

#VALUE!, #REF!, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!

Spill and newer functions

#SPILL!, #CALC!

Not an error value

#####, circular reference warning

Two of the 11 rows are not error values at all. A cell full of hash signs is a display problem, and a circular reference raises a warning when the formula is entered, with no code left in the cell.

Each Excel error code points to one cause

Type a code such as "#REF" or a cause such as "space" into the search box, and only the matching rows stay. The group switch narrows the rows further, and every column can be sorted.

Excel column width: ##### is not an error value

A cell full of hash signs means the value is wider than the column, or the formula returns a negative date or time. Excel hides a number or date it cannot fit, so the cell looks broken while the data underneath is fine. Neither the value nor the formula needs retyping, since selecting the right edge of the column header and dragging it to the width you want makes the value appear.

If widening does not help, the formula is returning a negative date or time, which Microsoft says can also show as #####. Subtracting a later date from an earlier one does this. Changing the cell to a number format shows the negative number behind the hash signs.

#VALUE!, #REF! and #DIV/0!: check the cells first

Each of the three common codes comes from what sits in the referenced cells, so the formula is usually fine and the cells need the fix. Text or a hidden space causes #VALUE!, a deleted reference causes #REF!, and a zero or blank divisor causes #DIV/0!.

What causes the three most common codes (per Microsoft Support)

#VALUE!

A cell in the calculation holds text or a hidden space

#REF!

A row, column or cell the formula pointed to is gone

#DIV/0!

The divisor is 0 or blank

#VALUE! means text or spaces broke the calculation

#VALUE! appears when a formula does math on something that is not a number. A cell that looks empty but holds one space counts as text. So does a dash or "n/a" typed into a cell that a multiplication uses.

Hidden spaces can be cleared in one pass. On the Home tab, Find & Select leads to Replace; put one space in Find what, leave Replace with empty, and replace all. A number stored as text carries a green triangle and sits on the left of its cell, which is the usual way to spot one.

When some text has to stay in the range, switch the operators for functions. SUM skips text where + fails, and PRODUCT does the same for *. If the cause is still unclear, Evaluate Formula on the Formulas tab runs the calculation one step at a time, so the step where it stops is visible.

#REF! appears after a referenced cell is deleted

#REF! means the formula points to a cell that no longer exists. Microsoft says this happens most often when referenced cells get deleted or pasted over. A SUM that lists cells one by one keeps the #REF! in the exact spot where the lost cell used to be.

Undo brings the cell back if the deletion just happened. When the file was already saved and closed, you can compare Excel files, the current workbook against an earlier copy, to see every cell that differs and which formulas turned into #REF!.

Writing a range instead of a list of cells prevents a repeat. A SUM over a range shrinks when a column inside it is deleted, because Excel adjusts the range itself. Lookups fail the same way when they ask for a column number larger than the range holds, and a formula that points at a closed workbook through INDIRECT returns #REF! too.

#DIV/0! appears when the divisor is 0 or blank

#DIV/0! shows up when a formula divides by zero, and an empty divisor cell counts as zero. A sheet that divides results by targets fills with this code for every row where no target has been entered yet. The formula is correct, and the input is simply missing.

For cells that will be filled in later, divide only when the divisor has a value. An IF test on the divisor leaves the cell blank while the divisor is 0 or empty and shows the result once a number arrives.

VLOOKUP #N/A and #NAME? errors and their fixes

#N/A is a result and #NAME? is a typing mistake, so the two need opposite fixes. With #N/A, the formula ran and the value it looked for is not there. With #NAME?, Excel could not read a name in the formula, so nothing ran.

#N/A and #NAME? are different problems

#N/A

The formula works, but the value it looked for isn't there

#NAME?

Excel can't read a name in the formula, so it never ran

#N/A means the lookup value was not found

Most #N/A results mean the lookup value really is missing from the lookup range. VLOOKUP, XLOOKUP and MATCH return it for a product code that is not in the list, and that is the correct answer until the code is added to the source data. When the value is definitely there, three checks come first:

• The lookup value is a number and the source column holds numbers stored as text, or the other way around
• One side has a leading or trailing space that cannot be seen
• The fourth VLOOKUP argument is TRUE or left out, so Excel is not asking for an exact match

Setting both columns to the same format fixes a type mismatch, and TRIM strips stray spaces. An exact match needs FALSE as the fourth VLOOKUP argument, or 0 as the MATCH match type.

#NAME? usually points to a misspelled function

#NAME? means Excel met a name in the formula that it does not recognize, and a misspelled function is the usual reason. Picking the function from the list that appears while typing avoids the typo. Four causes cover most cases: a misspelled function, text without double quotes, a range missing its colon, and a name that was never defined.

Text without quotes is the easiest to miss, because Excel treats it as a name and goes looking for it. Fixing the syntax is the only cure, since the formula cannot return a correct result until then. Microsoft tells readers not to hide this one with IFERROR.

#SPILL!, #CALC!, #NULL! and #NUM!: what blocks the result

The array codes point at an obstacle: a filled cell in the way, or a case Excel cannot calculate; the older codes point at a typo in a separator or a number. A spill is one formula returning several results that flow into the neighboring cells, as FILTER, UNIQUE and SORT do.

What blocks each code

#SPILL!

A formula that returns several results is blocked from filling cells

#CALC!

FILTER, LAMBDA or another array formula hits a case Excel can't calculate

#NULL!, #NUM!

A range separator or a number is written the wrong way

#CALC! most often means FILTER found nothing. FILTER returns it when no row matches, unless the formula gives an if_empty value to show instead. A LAMBDA that is defined but never called returns #CALC! as well, and calling it with an input fixes that.

#SPILL! means something blocks the spill range

#SPILL! means the cells the results need are not empty. Selecting the error cell draws a dashed border around the range Excel wants. Clearing whatever sits inside, including a stray space, lets the results fill in.

The error alert next to the cell has a Select Obstructing Cells option that jumps straight to the blocker. If the range looks empty, look at where the formula lives. Results cannot spill into merged cells, and spilled formulas do not work inside an Excel table, so unmerging the cells or moving the formula out of the table clears it.

Older formulas can trigger it too. A lookup written against a whole column now tries to return a result for every row on the sheet and runs past its edge. Pointing it at a single cell and copying it down fixes that.

#NULL! and #NUM! come from bad ranges or numbers

#NULL! comes from a space between two ranges that do not overlap, and #NUM! comes from a number the formula cannot use. Excel reads a space between ranges as "the cells where both ranges intersect", and with no intersection the result is empty. A colon joins one continuous range, and a comma separates two ranges.

For #NUM!, the usual culprit is a value typed as $1,000 inside a formula, where the dollar sign and comma already mean something else. Typing 1000 fixes it. IRR and RATE can return the same code when they cannot find an answer, and then raising the iterative calculation settings under File, Options, Formulas helps.

Circular reference in Excel: why the warning keeps coming back

A formula that uses its own cell triggers a warning instead of a code, and the warning stays until every circular cell is fixed. Excel cannot finish such a formula, because the cell would need its own result to calculate itself. A total typed into a cell that sits inside the total's own range does exactly this.

Error Checking lists the cells involved. On the Formulas tab, the arrow next to Error Checking opens a menu where Circular References shows them. Fixing the first cell and repeating until the status bar stops mentioning circular references clears the warning, and the same check has to cover other open workbooks, since a loop there keeps it alive.

Circular reference: warning, not an error value

Where it shows

A warning when the formula is entered, not a code in the cell

How to find it

Formulas, Error Checking arrow, Circular References

When it ends

The status bar no longer shows Circular References

Hide Excel errors with IFNA first, IFERROR second

IFNA hides only #N/A, so real errors stay visible, while IFERROR hides all seven codes and belongs only on formulas that are already checked. Hiding changes what a cell shows and fixes nothing underneath, so it comes after the formula is right.

Three ways to hide errors (IFERROR, IFNA, error checking)

IFERROR

Replaces all seven error codes with a value you choose

IFNA

Replaces only #N/A and leaves the rest visible

Error checking

Stops the green triangle from appearing

IFERROR replaces any error with a value you choose

IFERROR returns the value you pick when a formula errors, and the normal result when it does not. The whole formula goes in as the first argument, and a blank, a dash or a plain-words label such as "Not found" goes in as the second. The formula underneath stays the same either way.

The catch is that IFERROR covers #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. A #REF! from a deleted column and a #NAME? from a typo both turn into the same label, so a broken formula looks fine. Microsoft's own page on #VALUE! makes the point that IFERROR hides an error without resolving it.

IFNA hides #N/A and keeps real formula errors

IFNA is the safer choice for lookups, because it replaces only #N/A and every other code still shows. The syntax matches IFERROR, with the lookup formula as the first argument and the label as the second.

With IFNA, a row whose value is missing reads "Not found". A row where someone deleted the lookup column still shows #REF!, so a broken formula cannot hide behind a friendly label.

Turn off the green triangle error checking

Hiding errors with a formula leaves the green triangle in place, because the triangle comes from a separate background check. Excel puts the small triangle in a cell's top-left corner to mark cells it thinks might be wrong, including numbers stored as text, so it can sit on cells with no error value at all.

Under File, Options, Formulas, clearing Enable background error checking stops it. That also hides the triangle on real mistakes, so the setting suits a finished report better than a sheet still being built.

Summary: read the code, then fix before you hide

For Excel errors, the code shows where to look: fix the formula first, hide only the errors that are expected, and search the table for any code you do not recognize. Text or spaces cause #VALUE!, a deleted row or column causes #REF!, and a value missing from the lookup range causes #N/A.

• ##### is column width, and a circular reference is a warning, so neither needs an error-hiding formula.
• A lookup that simply finds nothing gets IFNA.
• A formula already tested and still expected to fail on some rows gets IFERROR.

For faster work in the same sheets, Excel shortcut keys cheat sheet, Windows and Mac covers the keys worth learning first. More Microsoft guides are on the topic page.