How to Fix Excel Errors: #REF!, #VALUE!, #NAME? and More

The Error Decoder

Errors cascade: any formula that references an error cell shows an error too. When a sheet lights up, fix the upstream cells first and the rest often clears on its own.

#REF! — The Formula Points at Something That No Longer Exists

1. Just happened? Press Ctrl+Z immediately — undo restores the deleted rows, columns or sheet and the reference with it.
2. Too late for undo: press Ctrl+H and Find what: #REF! — Excel finds every broken formula at once, so you can re-point each reference by hand.
3. If a whole named range was deleted, rebuild it under Formulas ▸ Name Manager and the formulas heal themselves.
4. For links to other workbooks that moved: Data ▸ Queries & Connections ▸ Edit Links and repoint to the new file location.

If #REF! appeared right after deleting rows or columns, do not save — close without saving and redo the deletion with the formulas in mind. A saved #REF! cannot be auto-restored.

#VALUE! — Right Formula, Wrong Ingredients

The formula is syntactically fine but is fed something it cannot digest: numbers stored as text (the green-triangle cells), invisible trailing spaces or non-breaking spaces pasted from the web, or dates that are actually text.

• Numbers stored as text: select the column ▸ click the warning icon ▸ Convert to Number — or bulk-fix the whole file in one pass with a converter.
• Hidden spaces: wrap the input in TRIM(), or strip trailing/non-breaking spaces from the entire sheet at once.
• Text dates: re-enter them in a real date format, or convert the column so date arithmetic works.

• Convert Text to NumbersBulk-fix text-formatted numbers across the whole file.
• Trim WhitespaceRemove trailing, leading and non-breaking spaces in one pass.

#NAME? — Excel Doesn’t Recognize That Word

• Typo in the function name: VLOKUP, SUMIFs with wrong casing of arguments typed from memory — check the spelling against the official syntax.
• Text typed without quotes: =IF(A1=yes,…) must be =IF(A1="yes",…).
• A named range that was renamed or deleted — verify under Formulas ▸ Name Manager.
• A function your Excel version doesn’t have: XLOOKUP, LET, FILTER and other dynamic-array functions show #NAME? in Excel 2019 and older. Replace them with INDEX/MATCH-style equivalents, or open the file in a newer Excel.

• Excel Functions ReferenceLook up the exact syntax of any function before retyping it.

#DIV/0! and #N/A — The Divide-and-Lookup Twins

• #DIV/0!: the divisor is zero or empty. Common with AVERAGE of an empty range or a percentage whose base is blank. Guard the formula:
• #N/A: VLOOKUP/XLOOKUP/MATCH found nothing. Either the value truly is absent, or it exists with a different type (number vs text) or trailing spaces — the two most common false alarms.
• When “nothing found” is a legitimate outcome, present it cleanly instead of as an error:

=IFERROR(A2/B2, "")        // blank instead of #DIV/0!
=IFNA(VLOOKUP(A2, D:E, 2, FALSE), "not found")   // text instead of #N/A

IFERROR hides the symptom, not the cause. Fix the underlying data first; use IFERROR only for cases where “not found / not applicable” is a real answer, not a surprise.

#####, #NUM!, #NULL! and Circular References

• ##### : the column is too narrow for the number — widen it. If it stays after widening, the value is a negative date or time, which Excel cannot display.
• #NUM!: the math is impossible or out of range — SQRT(-1), or a result beyond Excel’s limits. Check the inputs.
• #NULL!: a space between two ranges asked for an intersection that doesn’t exist, e.g. =SUM(A1:A5 C1:C5). Replace the space with a comma or colon.
• Circular reference: the formula’s own cell is part of its input. Excel names the cell in the status bar and in Formulas ▸ Error Checking ▸ Circular References. Move the formula or fix the logic — enable iterative calculation only if you intentionally designed a loop.

Stop Errors Before They Spread

• Validate at the door: restrict inputs to the type each column expects — numbers as numbers, dates as dates — so #VALUE! never gets raw material.
• Look up function syntax before writing, not after the error.
• Keep a clean copy: a version snapshot before big edits turns “everything shows #REF!” from a disaster into an undo.

• Validate Excel DataCheck types and spot bad inputs across the sheet, in the browser.