Excel Errors: What Each Code Means, and the Real Fix

An Excel error is not Excel breaking. It is Excel telling you, as specifically as it can, what went wrong — a deleted reference, text where it expected a number, a name it does not recognise. The instinct to wrap the whole formula in IFERROR and move on treats the message as noise. Read correctly, it is usually the fastest route to the actual fix, and that is true whether you fix it yourself or hand the formula to an AI tool to diagnose.

What each code is actually telling you

• —#REF! — the formula points at a cell, row or column that no longer exists, almost always because something was deleted. It is not a calculation problem; it is a missing-address problem.
• —#VALUE! — the formula is doing arithmetic on something that is not a number, most often a cell that looks like a number but is stored as text, or a cell with a trailing space.
• —#DIV/0! — the formula divided by zero or by an empty cell. Usually a downstream row has not been filled in yet, not a real calculation error.
• —#N/A — a lookup function (VLOOKUP, XLOOKUP, MATCH) searched for a value and did not find an exact match in the range you gave it.
• —#NAME? — Excel does not recognise a word in the formula, usually a misspelled function name or a named range that was renamed or deleted.
• —#NUM! — a number is outside what the function can handle, such as asking for a negative square root or an iterative calculation that never converges.
• —#SPILL! — a newer one: a dynamic array formula would overflow into cells that are already occupied, so it cannot spill its full result.

A worked example: VLOOKUP returns #N/A after a column insert

Weak prompt to an AI tool: "My formula is broken, fix it." This gives the model nothing to diagnose — it will usually guess, and a guessed fix on a formula is exactly where this goes wrong, covered below.

Better prompt: "This VLOOKUP returns #N/A: =VLOOKUP(A2,Sheet2!A:D,3,FALSE). Sheet2 column A has product codes, and I inserted a new column B last week. What does column index 3 point to now, and should it change?" Naming the exact formula, the real sheet layout and what changed is what lets the model actually locate the problem — a column insert shifts every index number after it, which is precisely the kind of #REF!/#N/A trigger a VLOOKUP cannot warn you about on its own. Excel lookup formula: VLOOKUP, XLOOKUP or INDEX/MATCH covers why XLOOKUP and INDEX/MATCH are less fragile against this exact failure, since they reference columns by name or position rather than a hardcoded index number.

Where asking AI to “just fix it” backfires

An AI tool can read a formula and an error code fluently, and it can also sound completely confident while pointing at the wrong cause — a well-documented property of how these models generate text, not something specific to spreadsheets. Two ways this shows up here specifically:

• —It suggests wrapping the formula in IFERROR without first identifying what is actually broken. That makes the error disappear from the cell, and the underlying wrong reference or mismatch stays wrong, now invisible.
• —It infers your sheet layout from the formula text alone, because you did not supply the real columns, and quietly assumes a structure that does not match your actual workbook — it will still return an answer, just not a correct one.

Both failures share one fix: name the actual sheet, the actual column letters or headers, and what recently changed, the same specificity habit how to write a prompt that works on the first try sets out for any task, not just this one. OpenAI’s and Anthropic’s own prompting guidance both make the same point: a vague description of a problem gets a vague, sometimes wrong, answer.

A formula that fails loudly with #REF! is an easy fix. One quietly wrapped in IFERROR is a wrong number nobody is looking for.

Checks before you trust the fix

1. 01Read the error code first and match it to the list above before asking anything to fix it — knowing which of the seven causes you are dealing with narrows the fix immediately.
2. 02Give the exact sheet name, column letters or headers, and anything that changed recently (an inserted row, a deleted column, a renamed tab) — a fix proposed without this is a guess.
3. 03Test the proposed fix on a copy of the workbook, not the live file, and compare its output against what you expect before replacing the original formula.
4. 04Never use IFERROR as the first move. Find the actual cause, fix that, and only wrap the result in IFERROR afterwards if a genuinely expected gap (a blank lookup value, for instance) needs a clean blank cell instead of an error.
5. 05If several formulas across the sheet are affected at once, check for a structural change — an inserted column, a renamed range — before treating each #REF! as a separate problem. One cause usually explains all of them, the same checking habit the NIST AI Risk Management Framework recommends building into any process where an automated step hands you an answer to trust.

What to do Monday

Next time a cell shows an error code, read it before you touch anything — it already told you the category of problem. Turning an Excel formula into text and Excel table: what Ctrl+T actually creates both cover structural habits — named ranges, structured references — that make several of these errors far less likely to happen in the first place. Code for Excel is worth reading next if the broken formula is actually a VBA macro or Office Script rather than a cell formula, since the debugging habit (name the exact thing that changed) is identical.

Reading an error message correctly instead of suppressing it is a small example of the same judgement behind the wage premium PwC has measured for workers with demonstrable AI skills — 62%, up from 57% the year before. Coursium teaches that judgement directly, one short lesson at a time. Stay ahead of AI by learning the tools on your phone.