How To Fix #N/A, #VALUE! And #REF! Errors In Excel (2026)

A cell that shows #N/A, #VALUE! or #REF! means Excel could not finish the formula in it.

Each message points to a different kind of problem, so the fix depends on which one you see.

The steps below follow Microsoft’s support pages, including the Error Checking tool and what to check first for each error.

Quick answer: what each error means

ErrorWhat Microsoft says it meansFirst thing to try
#N/AA value is not available to the formulaCheck the lookup value exists in the source data
#VALUE!The formula mixes incompatible data typesLook for text, spaces or text dates in the cells it uses
#REF!A cell reference is not validPress Ctrl+Z if you just deleted or pasted over cells
#DIV/0!A number is divided by zero or an empty cellGuard the formula with IF
#NAME?Excel does not recognize text in the formulaFix the spelling of the function or name

Fix #N/A: a value is not available

Microsoft says the most common cause is a lookup value that is not in the source data, with XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP or MATCH.

Either make sure the value exists, or wrap the formula in an error handler such as IFERROR.

CauseFix
VLOOKUP set to approximate matchSet range_lookup to FALSE for an exact match (for MATCH, set match_type to 0)
Number in one place, text in the otherMatch the formats with Format Cells (Ctrl+1)
Extra leading or trailing spacesWrap the range in TRIM, such as =VLOOKUP(D2,TRIM(A2:B7),2,FALSE)
Array formula ranges differ in sizeMake the ranges the same size, for example change B2:B5 to B2:B11
#N/A typed in or entered with NA()Replace it with real data when you have it

For a whole column of mismatched formats, Microsoft says to apply the format and then use Data > Text to Columns > Finish.

In older Excel, the TRIM version needs Ctrl+Shift+Enter, while current dynamic-array Excel accepts a normal Enter.

When #N/A is intentional

Microsoft notes that charts do not plot #N/A values.

That makes #N/A useful in place of 0 when you want to avoid a flat line on a chart.

Fix #VALUE!: the formula mixes data types

Microsoft says the error appears when a formula combines text and numbers with operators such as +, -, * or /.

CauseFix
Spaces that make a cell look blankSelect the column, go to Home > Find & Select > Replace, type one space in Find what, leave Replace with empty, and select Replace All
Hidden charactersFilter for Blanks (Home > Sort & Filter > Filter), select the blank cells that appear, and press Delete
Dates stored as textUse Data > Text to Columns to set a date format
Operators on mixed cellsUse a function such as SUM or PRODUCT instead of + or *
Windows list separator set to a minus signChange the List separator in Windows Region settings, then edit the cells
Broken data connectionRestore the connection, or ask the workbook creator for a values-only copy

Microsoft says a date that is left-aligned in its cell is usually text, so Excel will not use it in date arithmetic.

To find text cells, use ISTEXT in a helper column, which finds them but does not fix them.

Clean up text before you calculate

Microsoft suggests the CLEAN or REPLACE functions for stray characters, followed by Home > Paste > Paste Special > Values.

You may also need to convert numbers stored as text.

Microsoft has separate #VALUE! pages for AVERAGE, SUM, CONCATENATE, COUNTIF, DATEVALUE, IF, INDEX/MATCH, SUMIF, SUMPRODUCT, VLOOKUP and others if your formula uses one of those.

Fix #REF!: the reference is not valid

Microsoft says #REF! usually appears after cells a formula depends on are deleted or pasted over.

CauseFix
A row or column was deletedPress Ctrl+Z right away, or use a range such as =SUM(B2:D2) that adjusts when columns are deleted
Referenced cells were pasted overUndo the paste, or point the formula at valid cells
VLOOKUP column number too big, such as 5 for a 4-column rangeWiden the range, for example to A2:E5, or lower the number to 4
INDEX row or column outside the rangeUse numbers inside the range, such as =INDEX(B2:E5,4,4)
INDIRECT points to a closed workbookOpen the referenced workbook

Microsoft adds that a formula like =SUM(B2,C2,D2) breaks when one of those columns is deleted, but a range does not.

Microsoft says structured references and calculated references to linked workbooks are not supported.

If the link is an OLE link, start the program the link calls.

For a DDE link, confirm you are using the correct topic, and if the error persists, review your Trust Center external content settings.

The other common errors

ErrorCauseFix
####Column too narrow, or a negative date or timeDouble-click between the column headers to auto-fit, and check for negative dates or times
#DIV/0!Divide by zero or an empty cell=IF(C2,B2/C2,0)
#NAME?Misspelled function or range nameCorrect the spelling
#NULL!Space operator used on ranges that do not intersectSeparate ranges with a comma, as in =SUM(C2:C3,E4:E6)
#NUM!Invalid numeric valuesFor IRR or RATE, the function may not find a result

Use the Error Checking tool

  1. Go to Formulas > Formula Auditing > Error Checking.
  2. Pick an action for each error in the dialog box.
  3. Select Next to move to the next one.

Microsoft says to recalculate with F9 first if the sheet is set to manual calculation.

To fix one cell, select the Error Checking icon beside it and choose an option.

To bring back errors you told Excel to ignore, select Reset Ignored Errors, which applies to all sheets in the active workbook.

Turn on background error checking

On Windows, go to File > Options > Formulas and turn on background error checking, then pick the rules to apply.

On a Mac, Microsoft points to the Excel menu, then Preferences, then Error Checking.

Step through a formula with Evaluate Formula

  1. Select one cell.
  2. Go to Formulas > Formula Auditing > Evaluate Formula.
  3. Select Evaluate to step through each underlined part.
  4. Use Step In and Step Out to move into or out of referenced formulas.
  5. Select Restart to run it again, or Close to finish.

Microsoft lists this tool as Windows only.

Hide the error only as a last step

IFERROR can replace an error with a value you choose, such as 0.

Microsoft warns that it hides all errors, so the underlying problem is still there.

Fix the cause first, and use IFERROR only where an empty result is truly acceptable.

If formulas show as text instead of results, see fixing Excel formulas not calculating or showing text.

If a workbook hangs while you recalculate, read fixing Excel not responding or freezing.

If a bad edit wiped the data, see how to recover an unsaved Excel workbook.

Sources

Microsoft Support: Detect formula errors in Excel.

Microsoft Support: How to correct a #N/A error.

Microsoft Support: How to correct a #VALUE! error.

Microsoft Support: How to correct a #REF! error.

Leave a Reply

Your email address will not be published. Required fields are marked *