Excel Errors Explained: What #N/A, #VALUE!, #REF! and More Mean

Excel's error messages look cryptic, but each one is actually telling you something specific. Once you know what they mean, most take seconds to fix.

Not an error at all. The number or date is too wide to fit in the column. Double-click the right-hand border of the column letter to widen it.

If it's a date or time and widening doesn't help, the value is negative. Excel can't display negative dates.

A lookup (VLOOKUP, XLOOKUP, MATCH) couldn't find what you asked for. Common causes:

• The value really isn't in the list.
• Extra spaces, for example "Smith " with a space on the end. Use TRIM to clean them.
• Numbers stored as text in one list and as numbers in the other.
• VLOOKUP's range doesn't start at the lookup column.

To show a message instead, use IFNA: =IFNA(VLOOKUP(…), "Not found").

The formula expected a number and got text, or something it couldn't use. For example =A2+B2 where B2 contains "n/a" or a space.

Fix: find the cell containing text. Numbers that look fine but sit on the left of the cell are stored as text. Use SUM(A2:B2) instead of A2+B2, because SUM skips text instead of erroring.

A cell the formula was using has been deleted, often a whole row or column. The formula now shows #REF! where the reference used to be, e.g. =A2+#REF!.

Fix: press Ctrl + Z straight away to undo the delete. If it's too late, edit the formula and point it at the right cell again. It also happens when you copy a formula somewhere its references would go off the edge of the sheet.

You're dividing by zero, or by an empty cell, which Excel treats as zero. Very common in averages and percentages for rows that don't have data yet.

Fix: =IFERROR(B2/C2, 0) or =IF(C2=0, "", B2/C2).

Excel doesn't recognise something in the formula. Usually:

• A misspelt function, like =SUMM(A1:A5) or =VLOKUP(…).
• Text without quotation marks, like =IF(A2=Yes, …) instead of "Yes".
• A named range that doesn't exist.
• A newer function (like XLOOKUP) opened in an older version of Excel.

Tip: type function names in lower case. If Excel recognises them it converts them to capitals, so if it stays lower case, it's misspelt.

Newer functions like FILTER, SORT, UNIQUE and TRANSPOSE return several results that "spill" into the cells below and beside. If any of those cells already contain something, you get #SPILL!.

Fix: click the error, then click the warning icon and choose Select Obstructing Cells. Clear them. Spilling also won't work inside a Table. Move the formula outside it.

The result is impossible, like the square root of a negative number, or too big for Excel. It also appears when a function such as IRR can't find an answer.

Rare. You've put a space between two ranges, e.g. =SUM(A1:A5 C1:C5). Use a comma instead.

• Click the error cell and then the yellow warning icon for a description and options.
• Formulas > Evaluate Formula steps through the formula one piece at a time so you can see where it goes wrong.
• Formulas > Trace Precedents draws arrows to the cells the formula uses.
• Formulas > Error Checking jumps to each error on the sheet in turn.

Every error tells you where to look. #N/A means check your lookup values, #VALUE! means look for text, #REF! means something was deleted. And ##### just means widen the column.