Fix Excel Formula Errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and #SPILL!
Amir opens last month's report and sees #N/A in the lookups, #DIV/0! in the growth column and #REF! where a column used to be. Each error has a different cause, and the error name tells you where to look.
An error value is Excel telling you why it could not calculate. Read the error, click the cell, and use Formulas > Evaluate Formula or the small warning icon next to the cell to see which part fails. Fix the cause first; hide errors with IFERROR only when the error is expected.
Download the practice file (fix-formula-errors.xlsx)
Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Click the cell with the error and read the formula bar.
- Hover over the warning icon (yellow diamond) for Excel's hint.
- Use Formulas > Evaluate Formula to step through the calculation.
- Fix the cause using the table below.
- Only then, for expected cases like "not found", wrap with IFNA or IFERROR.
Error → cause → fix
| Error | Usual cause | Fix |
|---|---|---|
| #N/A | Lookup value not found (spelling, extra space, number stored as text) | Check the value with TRIM or VALUE; use IFNA(...,"Not found") if missing is normal |
| #VALUE! | Text where a number is needed, such as "1200 Rs" | Remove the extra text: =VALUE(SUBSTITUTE(B4," Rs","")), or fix the data type |
| #REF! | A cell or column the formula used was deleted | Undo (Ctrl + Z), or rewrite the reference; use Tables so formulas follow columns |
| #DIV/0! | Dividing by zero or an empty cell | =IF(C2=0,"",B2/C2) or =IFERROR(B2/C2,0) |
| #NAME? | Misspelled function or name, or text without quotes | Check spelling (SUMM → SUM); put text in quotes; check the name in Name Manager |
| #NUM! | Impossible maths, like the square root of a negative number, or IRR cannot find an answer | Check the inputs; give IRR a guess |
| #SPILL! | A dynamic array formula is blocked by cells that are not empty | Clear the cells in the dashed spill area |
| #### | Not an error: the column is too narrow, or a negative date | Widen the column (double-click the column border) |
The practice sheet
Column B has one broken formula per error and column C shows the fixed version. For example =B3/C3 with C3 empty gives #DIV/0!; =IF(C3=0,0,B3/C3) gives 0. =SUMM(B2:B4) gives #NAME?; =SUM(B2:B4) works.
Find every error on a sheet
- Press Ctrl + G (Go To) and click Special.
- Choose Formulas and tick only Errors. Click OK.
- Every error cell is selected. Press Tab to move through them.
- Or use Formulas > Error Checking to walk through errors one by one with an explanation.
IFERROR: use with care
=IFERROR(XLOOKUP(...),"") hides every error, including a typo you should fix. Prefer IFNA for lookups: it only catches #N/A (not found) and still shows #NAME? or #REF! when something is really broken.
Ravindra Bagale's Tip
Never hide errors in a report you have not checked. First make the errors visible, fix the real ones, and only then wrap the expected "not found" cases with IFNA.
Ravindra Bagale's Tip – मराठी
जो report तुम्ही तपासला नाही त्यात errors कधीच लपवू नका. आधी errors दिसू द्या, खऱ्या चुका दुरुस्त करा, आणि मगच अपेक्षित "not found" cases ना IFNA लावा.
Ravindra Bagale's Tip – हिंदी
जिस report को आपने जाँचा नहीं उसमें errors कभी मत छिपाइए। पहले errors दिखने दीजिए, असली गलतियाँ ठीक कीजिए, और फिर ही अपेक्षित "not found" cases पर IFNA लगाइए।
Practice task
- Open the Errors sheet. Read each broken formula in column E and its error.
- Compare with the fixed formula in column F.
- Use Ctrl + G > Special > Formulas > Errors to select all error cells.
- Fix the #VALUE! row: compare the broken
=B4*2with the cleaned VALUE(SUBSTITUTE(...)) version. - Delete column A on a copy of the sheet and watch #REF! appear in a formula that used it.
Learn it properly
Course lessons:
Related guides: IFERROR vs IFNA in Excel: Replace #N/A and #DIV/0! the Right Way · Excel VLOOKUP Exact Match · Excel Text Functions for Data Cleanup: TRIM, PROPER, LEFT, MID, FIND · SUBTOTAL and AGGREGATE: Totals That Ignore Hidden Rows and Errors
Frequently asked questions
Why does VLOOKUP show #N/A when the value is there?
Usually an extra space or a number stored as text. Compare with =LEN(A2) and =ISNUMBER(A2), then clean with TRIM or VALUE.
How do I stop #DIV/0! in percentage formulas?
Check the bottom number first: =IF(C2=0,"",B2/C2).
Can I recover from #REF! after saving?
Not automatically. Rewrite the reference, or restore an earlier copy from File > Info > Version History if the file is on OneDrive or SharePoint.
Is #### an error?
No. The column is too narrow to show the number or date. Widen it.