You've spent forty minutes building a report. You hit Enter on the last formula. Half the rows now say #N/A. The deadline is in twenty minutes. You don't know what #N/A means.
Every Excel error has a specific meaning. It is not Excel being broken. It is Excel telling you exactly what went wrong, in a language nobody bothered to translate.
Here are the six errors you'll actually meet in your first year on a corporate scheme. Each one means one specific thing. Each one is fixable in under a minute once you know what to look for.
Your XLOOKUP, VLOOKUP, INDEX/MATCH or similar formula went looking for a value and came back empty-handed.
Why it usually happens:
- A trailing space in the lookup value (
"Smith "doesn't equal"Smith") - One side is stored as text and the other as a number (the text
"12345"doesn't match the number12345, even though they look identical) - The value genuinely isn't in the source data
10-second fix
Wrap the lookup value in TRIM() to kill stray spaces: =XLOOKUP(TRIM(A2), range, return). Always add a fallback message so the cell shows something readable instead of #N/A: =XLOOKUP(A2, range, return, "Not found").
If TRIM doesn't fix it, check the alignment. If one column's values right-align (numbers) and the other left-aligns (text), you've got a type mismatch. Select the text column, then Data → Text to Columns → Next → Next → Finish to convert it to real numbers. Then the lookup matches.
If the value genuinely isn't there, that's a data problem, not a formula problem. Ask whoever sent you the file.
A formula was referencing a cell or range, and that reference no longer exists.
Why it usually happens:
- You deleted a column the formula was looking at
- You pasted over a cell containing a formula
- A linked workbook was moved or renamed
10-second fix
Press Ctrl + Z immediately if you've just made the change. Almost always works. If undo is gone, click the broken cell and look at the formula bar — you'll see #REF! sitting inside the formula itself where the missing reference should be. Replace it with the correct cell reference.
You asked Excel to do arithmetic on something that isn't a number.
Why it usually happens:
- A column that looks like numbers is actually stored as text (common after CSV imports)
- One cell in the range has a stray letter or symbol
- An invisible space character is making
"1000 "not equal1000
10-second fix
Look at the alignment. Real numbers right-align by default in Excel. If your "numbers" are left-aligned, they're text. Select the column → Data → Text to Columns → Next → Next → Finish. This forces Excel to re-read everything as the right type. Fixes the problem ninety per cent of the time.
A formula tried to divide by zero, or by a cell that's empty. Excel treats empty as zero.
Why it usually happens:
- The denominator hasn't been entered yet
- A percentage formula where the base value happens to be zero
10-second fix
Wrap the division in IFERROR():
=IFERROR(A2/B2, 0)
That returns 0 (or any fallback you choose) instead of the ugly red error. Senior analysts add IFERROR to every division formula by default.
Excel doesn't recognise something you typed as a function or a defined range.
Why it usually happens:
- You typed
=XLOOKUPP(...)with an extra P - You used a named range that no longer exists
- The formula needs an Excel add-in that isn't installed
10-second fix
Look at the formula bar. Excel highlights the section it can't parse. The typo is almost always obvious. To stop it happening again, always use the autocomplete — start typing =XL and let Excel finish the function name for you. Free spell-check, built in.
This isn't really an error. Excel knows the value, the column just isn't wide enough to show it. Beginners panic about this one most.
10-second fix
Double-click the right edge of the column header. The column auto-fits to the widest value in it. Works on any column, any time. To do it for every column at once: select all cells with Ctrl + A, then double-click any column border.
The summary reference
Print this, pin it to your second monitor, stop Googling:
| Error | What it means | The 10-second fix |
|---|---|---|
| #N/A | Lookup found nothing | Wrap in TRIM(), add a fallback |
| #REF! | Reference was deleted | Ctrl + Z, or replace the missing reference |
| #VALUE! | Text where a number is needed | Data → Text to Columns → Finish |
| #DIV/0! | Divided by zero or blank | Wrap in IFERROR(formula, 0) |
| #NAME? | Misspelled function name | Check spelling, use autocomplete |
| ##### | Column too narrow | Double-click the column edge |
Every error means one specific thing. Once you've seen each one a few times, the panic goes away.
The bigger lesson
None of these errors mean Excel is broken. They are Excel being unusually polite about telling you what's wrong.
The gap between an intern who freezes when a report breaks and a senior who fixes it in ten seconds isn't talent. It's that the senior has seen #N/A a thousand times and knows it's almost always trailing spaces. They're not smarter. They've just been embarrassed by the same error before.
Now you have the cheat code without the embarrassment.
The whole toolkit on one page.
XLOOKUP, SUMIFS, IFERROR, TRIM, the 15 shortcuts, the data-cleaning helpers, Mac and Windows. The 1-page Grid & Formula survival sheet has all of it. Printable PDF, instant download.
Get the sheet — £11