Excel Formula Errors Explained

Excel errors look alarming and are mostly mundane. This guide covers the six codes you will actually meet plus circular references, in rough order of how often they appear. One principle before the specifics: an error code is better than a wrong number. The genuinely dangerous spreadsheet failures are the silent ones — an error at least tells you where to look.

#REF! — the reference was destroyed

#REF! appears when a formula refers to cells that no longer exist. The formula did not break; the thing it pointed at was deleted.

The usual causes:

• Deleting a row, column or sheet that a formula referenced. =A2+B2 becomes =A2+#REF! when column B is removed.
• Pasting over cells that other formulas depend on.
• A VLOOKUP whose column number exceeds the width of its table — asking for column 5 of a 4-column range.

The fix: if you just deleted something, Ctrl+Z immediately — undo restores the reference intact, and it is the only clean repair. If the deletion is long past, the formula must be rewritten by hand, because #REF! erases the information about what was originally there. For the VLOOKUP case, reduce the column index or widen the range.

Prevention that actually works: convert data ranges to Tables (Ctrl+T) and reference columns by name. =SUM(Sales[Amount]) survives restructuring that would kill =SUM(C2:C500). Microsoft's #REF! reference covers the rarer causes.

#VALUE! — wrong kind of data

#VALUE! means a calculation received something it cannot process — most often text where a number was expected.

The causes, in order of likelihood:

• Numbers stored as text. The classic. Imported data frequently arrives as text that looks numeric. ="12"+3 works (Excel converts it), but a cell containing 12 with a trailing space, or a non-breaking space from a web page, does not convert.
• Invisible characters. Data pasted from websites and PDFs carries spaces, non-breaking spaces and stray characters you cannot see.
• Arithmetic on a cell containing text. =A1*2 where A1 says "n/a" or "TBD".
• Dates that are not really dates. "2026-08-29" stored as text subtracts as text, not as a date.

How to diagnose: select a suspect cell and check alignment — text left-aligns by default, numbers right-align. Or use =ISTEXT(A1), which returns TRUE for the impostors.

The fix: =VALUE(TRIM(A1)) converts a text-number to a real number and strips ordinary spaces. For a whole imported column, the fastest bulk repair is: type 1 in an empty cell, copy it, select the broken column, Paste Special → Multiply. Multiplying by 1 forces every convertible text-number into a real number in one step. Details in Microsoft's #VALUE! reference.

#N/A — the lookup found nothing

#N/A is almost always a lookup function reporting, correctly, that the value you searched for is not in the search range. It is the most honest error in Excel, and the one most often wrongly suppressed.

When the value genuinely is missing: the error is right. Handle it with =IFNA(VLOOKUP(...), "Not found") so the sheet reads cleanly. Use IFNA, not IFERROR — IFERROR also swallows #REF! and #VALUE!, hiding real defects behind friendly text.

When the value is visibly there and Excel still says #N/A, one of these is true:

• Trailing spaces on one side. "Smith " does not match "Smith". Wrap both sides in TRIM().
• Text versus number. The digits 1050 stored as text will not match the number 1050. Check alignment, or compare =ISTEXT() on both.
• Approximate match returning nothing sensible. A VLOOKUP without FALSE as the fourth argument on unsorted data. Always write the FALSE.

Our VLOOKUP vs XLOOKUP guide covers the lookup traps in depth; Microsoft's #N/A reference lists the rest.

#DIV/0! — division by zero or by nothing

Exactly what it says: the divisor is zero — or, just as often, an empty cell, which Excel treats as zero. An average over rows that have not been filled in yet is the everyday case.

The fix depends on which situation you are in:

• The divisor should never be zero — then the error has caught a data problem. Find out why the cell is empty; do not paper over it.
• Zero or empty is legitimate (a month with no sales yet): guard it. =IF(B2=0, "", A2/B2), which shows nothing until data exists.

The pattern to avoid is wrapping everything in IFERROR reflexively. A sheet in which no formula can ever show an error is a sheet in which real mistakes are invisible.

#NAME? — Excel does not recognise a word

#NAME? means something you typed is not a function, range name or valid reference. Causes, most common first:

• A misspelled function. =VLOKUP(...).
• A function your Excel version does not have. =XLOOKUP in Excel 2019 or older gives #NAME?, not a graceful fallback. This matters when a file made in Microsoft 365 is opened elsewhere.
• Text without quotation marks. =IF(A1=yes,1,0) — Excel reads yes as a name. It needs "yes".
• A deleted named range still referenced by formulas.

The version case is worth remembering in both directions: if a colleague reports #NAME? on a sheet that works for you, compare Excel versions before debugging the formula.

##### — not an error at all

A column of ##### means the column is too narrow to display the number. The value underneath is intact. Double-click the right edge of the column header to auto-fit, and it reappears.

The one real problem it can flag: a negative date or time — subtracting a later date from an earlier one — also displays as ##### no matter how wide the column. If widening does not fix it, check the date arithmetic.

Circular references — the formula that needs its own answer

A circular reference is a formula that depends, directly or through a chain, on its own cell. =A1+B1 typed into B1 is the direct case; longer loops through several cells are the common one. Excel warns once, then shows 0 and a "Circular References" note in the status bar.

Finding it: Formulas tab → Error Checking → Circular References lists the cells involved. For a chain, fixing the one cell that should not be in the loop breaks the whole circle.

The usual accident: a SUM whose range includes its own row — =SUM(C2:C20) placed in C20. Move the total outside the range it sums.

Deliberate circular references (iterative calculations) exist but are rare and off by default; Microsoft's circular reference guide covers enabling iteration if you genuinely need it.

Tracing an error to its source

Errors propagate: one #VALUE! feeds every formula downstream of it, so a sheet can light up with twenty errors that all share one cause. Work upstream, not downstream.

1. Select an error cell and press Ctrl+[ — jumps to the cells that formula references. Repeat until you reach a cell whose error is original rather than inherited.
2. Or use Formulas → Trace Precedents for the same thing drawn as arrows.
3. Formulas → Evaluate Formula steps through a calculation one operation at a time and shows exactly where the error enters.

Fix the first error in the chain and the rest usually clear themselves.

If the cell shows no error at all but simply will not produce a result, that is a different problem: why an Excel formula is not calculating covers Show Formulas, manual calculation and the text-format trap.

Working in Google Sheets instead? Google Sheets formulas not updating covers what differs there — most importantly that Sheets has no manual calculation mode, so the usual Excel fix does not apply.

If the formula runs but gives the wrong answer rather than an error, the cause is often condition order: Excel IF with multiple conditions covers AND, OR and IFS, and why the order of tests changes the result.

Frequently asked questions

How do I fix a #REF! error in Excel?

If it just appeared, press Ctrl+Z — undo restores the deleted cells the formula pointed at, and is the only clean fix. Otherwise the formula must be rewritten, because #REF! destroys the record of what was referenced. Using Tables with column names prevents most future cases.

Why does my formula show #VALUE!?

Almost always because text reached an arithmetic operation — usually numbers stored as text after an import, or invisible spaces from pasted data. Check whether the cells left-align (text) rather than right-align (numbers), and repair with =VALUE(TRIM(cell)) or by multiplying the column by 1 via Paste Special.

Should I wrap formulas in IFERROR?

Sparingly. IFERROR hides every kind of error, including the #REF! and #VALUE! that indicate genuine defects. For lookups, prefer IFNA, which only catches "not found" and lets real errors stay visible.

Why does #N/A appear when the value is clearly in my table?

The two values are not actually equal: one has a trailing space, or one is text and the other a number. Wrap both sides in TRIM(), and compare ISTEXT() on each. This accounts for nearly every "but I can see it!" lookup failure.

What do ##### symbols mean?

The column is too narrow to show the number — the data is fine. Double-click the column edge to auto-fit. The one exception: a negative date or time also renders as #####, and no width fixes that.

How do I find a circular reference in Excel?

Formulas tab → Error Checking → Circular References lists the cells in the loop. The most common cause is a SUM placed inside the range it adds up — move the total outside its own range.

Conclusion

Each code narrows the problem for you: #REF! says look at what was deleted, #VALUE! says look for text pretending to be numbers, #N/A says the lookup is honest and the data is not. Trace upstream with Ctrl+[, fix the first error in the chain, and resist the urge to bury errors under IFERROR — a visible error is a spreadsheet telling you the truth.

Related: VLOOKUP vs XLOOKUP for the lookup errors specifically, and converting units in Excel for the CONVERT function's own #N/A behaviour.