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
| Error | What Microsoft says it means | First thing to try |
|---|---|---|
| #N/A | A value is not available to the formula | Check the lookup value exists in the source data |
| #VALUE! | The formula mixes incompatible data types | Look for text, spaces or text dates in the cells it uses |
| #REF! | A cell reference is not valid | Press Ctrl+Z if you just deleted or pasted over cells |
| #DIV/0! | A number is divided by zero or an empty cell | Guard the formula with IF |
| #NAME? | Excel does not recognize text in the formula | Fix 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.
| Cause | Fix |
|---|---|
| VLOOKUP set to approximate match | Set range_lookup to FALSE for an exact match (for MATCH, set match_type to 0) |
| Number in one place, text in the other | Match the formats with Format Cells (Ctrl+1) |
| Extra leading or trailing spaces | Wrap the range in TRIM, such as =VLOOKUP(D2,TRIM(A2:B7),2,FALSE) |
| Array formula ranges differ in size | Make 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 /.
| Cause | Fix |
|---|---|
| Spaces that make a cell look blank | Select the column, go to Home > Find & Select > Replace, type one space in Find what, leave Replace with empty, and select Replace All |
| Hidden characters | Filter for Blanks (Home > Sort & Filter > Filter), select the blank cells that appear, and press Delete |
| Dates stored as text | Use Data > Text to Columns to set a date format |
| Operators on mixed cells | Use a function such as SUM or PRODUCT instead of + or * |
| Windows list separator set to a minus sign | Change the List separator in Windows Region settings, then edit the cells |
| Broken data connection | Restore 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.
| Cause | Fix |
|---|---|
| A row or column was deleted | Press Ctrl+Z right away, or use a range such as =SUM(B2:D2) that adjusts when columns are deleted |
| Referenced cells were pasted over | Undo the paste, or point the formula at valid cells |
| VLOOKUP column number too big, such as 5 for a 4-column range | Widen the range, for example to A2:E5, or lower the number to 4 |
| INDEX row or column outside the range | Use numbers inside the range, such as =INDEX(B2:E5,4,4) |
| INDIRECT points to a closed workbook | Open 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.
Other causes of #REF!
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
| Error | Cause | Fix |
|---|---|---|
| #### | Column too narrow, or a negative date or time | Double-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 name | Correct the spelling |
| #NULL! | Space operator used on ranges that do not intersect | Separate ranges with a comma, as in =SUM(C2:C3,E4:E6) |
| #NUM! | Invalid numeric values | For IRR or RATE, the function may not find a result |
Use the Error Checking tool
- Go to Formulas > Formula Auditing > Error Checking.
- Pick an action for each error in the dialog box.
- 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
- Select one cell.
- Go to Formulas > Formula Auditing > Evaluate Formula.
- Select Evaluate to step through each underlined part.
- Use Step In and Step Out to move into or out of referenced formulas.
- 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.
Related Excel guides
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.