Home › Excel basics › Formula errors

Excel Formula Errors and How to Fix Them

An error in Excel always starts with # and tells you what went wrong. #N/A means a value wasn't found, #VALUE! means the wrong type of data, #REF! a deleted reference and #DIV/0! a division by zero. Here's every error, what causes it and how to fix it.

Excel CSE · 4 min read

#N/A: value not available

Lookup functions like VLOOKUP, XLOOKUP and MATCH return #N/A when they can't find what you're looking for.

• The value really isn't in the list. Then #N/A is correct; show a friendly message with =IFNA(VLOOKUP(…), "Not found").
• Extra spaces: "Anna " doesn't match "Anna". Clean up with TRIM.
• A number stored as text on one side. See convert text to numbers.
• The lookup range moved down when you filled the formula. Lock it with $ signs.

#VALUE!: wrong type of value

A formula expects a number or date and gets text. ="abc"+1 gives #VALUE!.

• Look for text that looks like a number, or a cell with a space in it.
• Use SUM instead of +: =SUM(A2:A5) skips text, =A2+A3+A4+A5 doesn't.
• Dates typed in a format your Excel doesn't recognise are text. Check with =ISNUMBER(A2).

#REF!: invalid reference

The formula refers to a cell, row, column or sheet that was deleted. =A2+B2 becomes =A2+#REF! after you delete column B.

• Press Ctrl+Z straight away to undo the deletion.
• Otherwise, edit the formula and replace #REF! with the right cell.
• VLOOKUP with a column number larger than the table also gives #REF!.
• Find all of them: Ctrl+F, search for #REF!, Look in: Formulas, Find All.

#DIV/0!: division by zero

You divide by 0 or by an empty cell, for example a percentage while the total is still empty. Show a blank instead:

AVERAGE also returns #DIV/0! when there are no numbers to average.

#NAME?: unknown name

• A typo in the function name: =SUMM(A1:A5).
• Text without quotes: =IF(A2=Yes, …) must be "Yes".
• A named range that doesn't exist (anymore).
• A newer function such as XLOOKUP or FILTER in an older Excel version that doesn't have it.

#NUM!: invalid number

The calculation is mathematically impossible or the number is too large: =SQRT(-4), =DATEDIF with the start date after the end date, or =10^400. Financial functions like IRR give #NUM! when they can't find an answer; try giving a guess as the last argument.

#SPILL!: result can't spill

Dynamic array formulas like FILTER, UNIQUE and SORT need empty cells for their result. Click the error to see a dashed border of where it wants to go, and clear those cells. Spilling also doesn't work inside an Excel table (Ctrl+T).

#CALC!: empty result

Most often FILTER found no matching rows. Use its third argument: =FILTER(A2:C20, B2:B20="East", "No results").

#NULL!: ranges don't intersect

A space between two ranges means "where they overlap". =SUM(A1:A5 C1:C5) has no overlap. Use a comma for separate ranges (A1:A5, C1:C5) or a colon for one range.

#####: not really an error

The number or date doesn't fit. Double-click the right edge of the column header to widen it. If it stays ####, the result is a negative date or time, which Excel can't show; see time difference for shifts past midnight.

Circular reference warning

A formula refers to its own cell, directly or through other cells: =SUM(A1:A10) in A10. Go to Formulas › Error Checking › Circular References to see which cell causes it, and change the range so it stops one row higher.

Hide errors with IFERROR

IFERROR replaces any error with the value you choose. Use it once you know why the error appears: it also hides real mistakes, such as a typo in a range. For lookups, IFNA is safer because it only catches #N/A.