Excel #REF!, #VALUE! and #NAME? Errors: Why Your Formulas Return Codes Not Numbers

Fix it now

An error value is the calculation engine naming a specific fault, not file damage. The workbook is fine; one formula, or one cell it reads, is wrong, and the value tells you which kind of wrong.

1. Click the cell showing the error and read the formula bar. The value gives you the category before you look at anything else.
2. With the cell selected, use Formulas, Formula Auditing, Evaluate Formula and click Evaluate repeatedly. The step at which the result turns into an error is the broken part.
3. If the error appeared the moment you deleted rows, columns or a sheet, press Ctrl+Z now. #REF! is written permanently into the formula text and undo is the only thing that restores the original address.
4. For #VALUE!, compare =COUNT(range) with =COUNTA(range) over the cells the formula reads. A lower COUNT means some entries are text rather than numbers.
5. For #NAME?, check every function spelling, then open the Name Manager and confirm each defined name in the formula still exists.
6. To find every error at once, press F5, choose Special, select Formulas and untick everything except Errors.

Two of the six are structural and four are data-driven. Tidying the data will not fix #REF! or #NAME?, and rewriting the formula will not fix a column of numbers stored as text.

If Go To Special finds no error cells and the totals check out, you are done. The next section explains what each value reports and why the cell you are looking at is often not the one at fault.

Why it happens

Excel resolves references, converts arguments to the types each function expects, then performs the operation. An error is returned by whichever stage failed, and it propagates: a cell that merely totals ten others shows the same error as the one broken cell inside that range. The cell you are staring at is frequently not the cell at fault, which is why Trace Precedents is worth learning before anything else.

Two values are structural. Microsoft describes #REF! as showing when a formula refers to a cell that is not valid, and it is the harsher of the two: when you delete the cells a formula points at, Excel writes the token into the formula text and the original address is gone from the file. #NAME? means Excel does not recognise text in a formula – most often a typo in a function name, but also a defined name that does not exist, missing quotation marks around a text value, a range reference missing its colon, or a function that needs an add-in which is not enabled.

The other four are data-driven. Microsoft’s own summary of #VALUE! is that there is something wrong with the way the formula is typed, or with the cells it references, and the documented causes go well beyond obvious type mismatches: hidden spaces and characters that make a cell look blank, dates stored as text, leading spaces before dates, and broken connections to external data. #DIV/0! is exactly what it says – a number divided by zero, or by a cell containing zero or nothing.

#N/A generally indicates that a formula cannot find what it has been asked to look for, and it is often not an error at all: a lookup reporting an absent value, or the NA function entered deliberately as a placeholder. #NUM! appears when a formula or function contains numeric values that are not valid, which covers three quite different things – values typed with currency symbols or commas, iterative functions such as IRR or RATE that cannot find a result, and a result outside the range Excel can represent.

Full reference

Reading the symptom back to the fault

The six values, as Microsoft describes them

A seventh value, #NULL!, appears when you specify an intersection of two areas that do not intersect. It is rare, and it almost always means a space was typed where a comma was meant.

The #NUM! cases people miss

• A value typed with a currency symbol or thousands separators, because commas are reserved for formula syntax.
• An iterative function such as IRR or RATE that cannot find a result after its calculation attempts, which usually means the inputs cannot converge rather than that the formula is wrong.
• A result too large or too small to represent. Excel’s documented range is between -1 x 10 to the power 307 and 1 x 10 to the power 307.
• A function given an argument outside its accepted domain, such as a negative number where a square root is expected.

Hidden characters, in order of likelihood

1. Trailing spaces from a copy and paste, which TRIM removes.
2. Non-breaking spaces from a web page, character 160, which TRIM does not remove and SUBSTITUTE does.
3. Non-printing control characters from an old export, which CLEAN removes.
4. A leading apostrophe, which forces text and is invisible in the cell but visible in the formula bar.
5. A number formatted as text by the column’s own format, which no formula fixes: change the format, then re-enter or re-parse the values.

Finding and checking the lot

Go To Special with Formulas and only Errors ticked selects every error cell in the sheet at once, which is the fastest audit there is. Do that before and after any fix, and force a full recalculation between the two so nothing is showing a stale cached result. Then spot-check two or three totals against a known figure, because a suppressed error returns a plausible number rather than an obvious one, and plausible is worse.

Every code this article covers

Confirm the fix worked

1. Go To Special with Formulas and only Errors ticked finds no cells.
2. A full recalculation has been forced, so nothing is showing a stale cached result.
3. Two or three totals match a known figure, in case a suppressed error now returns a plausible but wrong number.
4. Saving, closing and reopening the workbook does not bring the errors back on load.
5. Any IFERROR you added wraps a formula you have confirmed is correct, not one you have not checked.

Questions people ask about this