THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Excel Formulas & Functions

Excel Formula Errors Explained: #N/A, #VALUE!, #REF!, and More

Learn what common Excel formula errors mean, including #N/A, #VALUE!, #REF!, #DIV/0!, and #NAME?, plus the right first fix.
Ethan Mercer
By:

Excel formula errors are useful clues. A value such as #N/A, #VALUE!, or #REF! tells you what Excel could not calculate, so the right fix depends on the specific error.

Start by selecting the error cell and reading the formula bar. Then use this guide to identify the cause and make the smallest safe correction.

Common Excel formula errors at a glance

Excel formula error quick guide for #N/A, #VALUE!, #REF!, #DIV/0!, and #NAME? errors.
Each error code points to a different problem, so diagnose it before adding an error-handling formula.

#N/A: Excel cannot find a match

#N/A usually appears in a lookup formula when the value you asked Excel to find is not present in the lookup range. It is common with XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, and MATCH.

For example, this formula returns #N/A if product code P-104 is not in A5:B20:

=VLOOKUP("P-104",$A$5:$B$20,2,FALSE)

Before changing the formula, check these points:

  • The lookup value actually exists in the source data.
  • The lookup range includes the value and the column you want to return.
  • Text and numbers are stored consistently. For example, text 104 is different from numeric 104.
  • There are no leading, trailing, or hidden spaces in the lookup value.

Use IFNA or IFERROR only when a missing match is expected and you have decided what the reader should see instead. Do not use it to hide an unexplained lookup problem.

#VALUE!: a formula received the wrong kind of data

#VALUE! often means a formula expected a number, date, or valid text argument but received something else. A cell that looks blank can still contain a space or hidden character, and a number copied from another system can be stored as text.

Start by checking the cells used by the formula. To test whether a cell contains text, use:

=ISTEXT(A2)

If it returns TRUE where you expected a number, clean or re-enter the value. For imported data, remove unnecessary spaces and convert numbers stored as text before changing the formula itself.

#REF!: a reference is no longer valid

#REF! appears when a formula refers to a cell, range, sheet, or workbook reference that is no longer valid. Deleting or pasting over a referenced row or column is a common cause.

Excel #REF! error example showing a deleted reference in a SUM formula and a corrected range formula.
When a formula contains #REF!, restore the intended reference instead of hiding the error.

If you accidentally deleted a needed row or column, use Undo immediately. Otherwise, select the error cell, find #REF! in the formula bar, and replace it with the intended reference. Check the result before filling the corrected formula into other cells.

#DIV/0!: the formula divides by zero or a blank

#DIV/0! occurs when a formula divides by zero or by an empty cell. For example, =B2/C2 returns this error if C2 is zero or blank.

If a blank denominator is normal in your worksheet, test it before dividing:

=IF(C2,B2/C2,0)

This returns zero when C2 is blank or zero. Use that result only when zero is meaningful for your situation; otherwise, return a clear message or leave the formula error visible until the source value is fixed.

#NAME?: Excel does not recognize part of the formula

#NAME? means Excel cannot recognize text in the formula. Common causes include a misspelled function name, an undefined named range, missing quotation marks around text, or a function that is not available in your Excel version.

Check the spelling in the formula bar and use Excel’s function suggestions as you type. For example, use =SUM(A1:A5), not =SMM(A1:A5). If the formula contains text, put that text in quotation marks:

=IF(A2>100,"Over target","On track")

What does #### mean in Excel?

Four hash marks usually do not mean the formula is wrong. The column may simply be too narrow to display the value. Double-click the right edge of the column heading to AutoFit it. Excel can also show #### for negative date or time values.

Fix the first error before using IFERROR

Errors can flow through a workbook. If one lookup returns #N/A, another formula that depends on it can also return an error. Start with the first source error, correct the data or reference, and then recalculate.

For a hard-to-read formula, use Formulas > Evaluate Formula in Excel desktop to inspect the calculation step by step. If a formula does not update but does not show an error, use our Excel formula troubleshooting guide. To understand how copied references change, read Excel Cell References: Relative, Absolute, and Mixed.

Source and further reading

Microsoft provides detailed help for detecting formula errors, correcting #N/A, correcting #VALUE!, and correcting #REF! errors.