Excel formula errors (#N/A, #VALUE!, #REF!, #DIV/0!) and how to fix them

Few things are as puzzling in Excel as opening a sheet and finding cells full of #N/A, #VALUE! or #REF!. These messages are not bugs in the program: they are warnings that a formula cannot calculate what you are asking for, and each one points to a different cause.

Once you know how to read them, most can be fixed in a minute. Below you will find what each error means, why it appears, examples with corrected formulas and how to keep it from coming back.

Quick summary: what each error tells you

To find the source, select the cell with the error and use Formulas › Evaluate Formula: you will see the calculation step by step and exactly where it fails.

#N/A: the value you’re looking for doesn’t exist (or seems not to)

It shows up mainly with VLOOKUP, XLOOKUP, MATCH and INDEX when the lookup value is not in the list. Example: on the Prices sheet you have product codes in column A and prices in column B, and on another sheet you look up the code in cell A2:

=VLOOKUP(A2,Prices!A:B,2,FALSE)

If the result is #N/A and you are sure the code exists, check these causes:

• Invisible spaces: “P-101 ” with a trailing space is not the same as “P-101”.
• Numbers stored as text in one of the two lists: 101 and “101” don’t match.
• Missing last argument: without FALSE, VLOOKUP performs an approximate match and can return odd results or #N/A if the list isn’t sorted.
• Incomplete lookup range that doesn’t include every row.

For spaces, you can clean the lookup value inside the formula itself:

=VLOOKUP(TRIM(A2),Prices!A:B,2,FALSE)

When the #N/A is legitimate (the code really doesn’t exist), show a clear message instead of the error. In Excel 365 and 2021, XLOOKUP has a dedicated argument for this:

=XLOOKUP(A2,Prices!A:A,Prices!B:B,"Not found")

In earlier versions, use IFNA (available since Excel 2013), which only catches #N/A and lets other errors show:

=IFNA(VLOOKUP(A2,Prices!A:B,2,FALSE),"Not found")

#VALUE!: the data isn’t the expected type

Excel displays #VALUE! when an operation receives something it can’t use, usually text where it expects a number. A typical example:

Common causes:

• Units or symbols typed by hand next to the number (“95 €”, “10 kg”).
• Numbers imported from another system as text, sometimes with a non-breaking space you can’t see.
• Adding cells that contain text with the + sign: =B2+B3 fails, while =SUM(B2:B3) ignores the text (although the total will then be wrong without warning you).

The fix is to keep only the number in the cell and apply the symbol through number formatting. To convert text to numbers, use Data › Text to Columns and click Finish straight away, or the VALUE function. If the imported numbers contain non-breaking spaces (very common in data copied from a web page), replace them first:

=VALUE(SUBSTITUTE(B3,CHAR(160),""))

#REF!: the reference no longer exists

This is the most dangerous error, because it usually appears after something has been deleted. If C2 contains =A2+B2 and you delete column B, the formula becomes =A2+#REF! and stops calculating.

Other common causes:

• Copying a formula to a place where relative references fall off the sheet, for example a reference to the row above copied into row 1.
• Deleting the sheet the formula points to.
• Asking VLOOKUP for a column that doesn’t exist in the range. This formula returns #REF! because the range A:B only has two columns:

=VLOOKUP(A2,Prices!A:B,3,FALSE)

What to do when it appears:

1. If you have just deleted something, undo with Ctrl+Z. It is the quickest fix.
2. Otherwise, find every affected cell with Home › Find & Select › Find: type #REF! and choose Look in: Formulas.
3. Enter the correct reference in the first cell and copy the formula to the rest again.

To prevent it, work with tables and structured references or with named ranges, which cope better with columns being inserted and deleted. And before deleting a column, check whether any formula depends on it with Formulas › Trace Dependents.

#DIV/0!: dividing by zero or by an empty cell

It appears when calculating percentages, averages or ratios and the divisor is 0 or empty. For example, the average price of a product with no sales: =B2/C2 with C2 empty. AVERAGE also returns it if the range contains no numbers at all.

The cleanest solution is to check the divisor before dividing:

=IF(C2=0,"",B2/C2)

That way the cell stays empty until there is data. If you’d rather show a zero or a text such as “No sales”, change the second argument. Avoid typing the result by hand: as soon as the data arrives, the formula should work on its own.

#NAME?: Excel doesn’t recognize part of the formula

#NAME? means Excel doesn’t recognize something written in the formula:

• A misspelled function, such as =SUMM(B2:B10) instead of =SUM(B2:B10).
• Text without quotation marks: =IF(A2=Yes,1,0) should be written =IF(A2="Yes",1,0).
• A function your version doesn’t have: if you open a file that uses XLOOKUP or FILTER in Excel 2016, you will see this error when it recalculates.

IFERROR: use it wisely

IFERROR replaces any error with the value you specify:

=IFERROR(B2/C2,0)

It is convenient, but there is a risk: it hides every error, including those that signal a real problem, such as a broken reference or badly imported text. A few tips:

• Apply it only when you know which error you expect and why.
• For lookups, prefer IFNA or the XLOOKUP argument, which only handle #N/A.
• Don’t add it when you first write the formula: check that it works without it first.

When the errors come from the file itself

If formulas that used to work show errors when you open a workbook, or Excel warns that it found content it can’t read, the problem may lie in the file rather than in the formulas. It happens with incomplete downloads, interrupted copies or files saved from other programs. First try File › Open, select the file and choose Open and Repair from the arrow on the Open button.

If that isn’t enough, our free tool to repair Excel files tries to recover the data from damaged xls, xlsx and csv files right in your browser. If the recovered workbook is an old .xls file, it’s a good idea to convert it to XLSX afterwards, Excel’s current format.

Conclusion

Every Excel error is a clue: #N/A is about lookups, #VALUE! about data types, #REF! about broken references, #DIV/0! about empty divisors and #NAME? about something misspelled. Before covering them up with IFERROR, understand the cause with Evaluate Formula, fix the data or the reference, and save error handling for the cases that really need it. Your sheets will be more reliable and easier to review.

Frequently asked questions