Excel Error Messages Explained: #N/A, #REF!, #VALUE! and More
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016
An error value in a cell is Excel telling you exactly what went wrong, if you know how to read it. Each error code points to a different cause: a missing value, a deleted cell, a typo in a function name, or a division by zero. This guide explains every common error, shows a typical example of how it happens and gives the fix. It finishes with tools that help you trace errors through a large workbook.
#N/A: value not available
#N/A almost always comes from a lookup function such as VLOOKUP, XLOOKUP or MATCH that could not find what it was looking for.
Typical causes and fixes:
- The value really is missing from the lookup table. Add it, or show a message with
=IFNA(VLOOKUP(A2,Prices,2,FALSE),"Not in price list"). XLOOKUP has a built-in fourth argument for this. - Extra spaces. "P-100 " does not match "P-100". Use
=TRIM(A2)to clean the data; see text functions. - Numbers stored as text on one side and real numbers on the other. A product code 1001 typed as text will not match the number 1001. Convert one side so both match.
- The lookup range moved when the formula was copied. Lock it with dollar signs:
$A$2:$C$200.
#VALUE!: wrong type of value
#VALUE! appears when a formula expects one kind of value and receives another, most often text where a number should be.
=A2+B2where B2 contains "N/A" or "five" returns #VALUE!. Use=SUM(A2:B2), which ignores text, or correct the cell.- Dates imported as text, such as "31.12.2026", cannot be used in date calculations. Convert them with Data > Text to Columns, choosing the correct date order in step 3.
- Ranges of different sizes in SUMIFS or COUNTIFS also return #VALUE!.
- Invisible characters copied from web pages, such as non-breaking spaces, can be removed with
=SUBSTITUTE(A2,CHAR(160),"").
#REF!: invalid reference
#REF! means a formula points to a cell that no longer exists. The usual cause is deleting a row, column or sheet that a formula used. If =B2*C2 is in D2 and you delete column C, the formula becomes =B2*#REF!.
- Fix immediately with Ctrl + Z to undo the deletion.
- Otherwise, edit the formula and point it at the correct cell again.
- VLOOKUP with a column number larger than the table width, such as 5 for a three-column table, also returns #REF!.
- Prevention: clear contents instead of deleting columns that formulas use, and prefer XLOOKUP or INDEX/MATCH, which refer to whole columns rather than a column count.
#DIV/0!: division by zero
Any formula that divides by zero or by an empty cell returns #DIV/0!. It is common in percentage and average columns where some rows have no data yet.
=IF(B2=0,"",A2/B2)leaves the cell blank until there is something to divide by.=IFERROR(A2/B2,0)shows zero instead. Use this only when you are sure the formula is otherwise correct, because IFERROR hides all errors.- AVERAGEIFS returns #DIV/0! when no rows match the conditions, for the same reason.
#NAME?: Excel does not recognise something
#NAME? means Excel found text in a formula that it cannot interpret.
- A misspelled function:
=SUMM(A2:A10)or=VLOKUP(...). Type the first few letters and choose the function from the list that appears to avoid typos. - Text without quotes:
=IF(A2=Paid,1,0)needs"Paid". - A function your version does not have. XLOOKUP, FILTER, UNIQUE and LET need Microsoft 365 or Excel 2021. Files opened in older versions show #NAME? for these.
- A deleted or misspelled named range. Check names in Formulas > Name Manager.
- A missing colon in a range, such as
A2A10.
#NUM! and #NULL!
#NUM! appears when a calculation produces an impossible or enormous number, for example =SQRT(-4), or when functions such as IRR or RATE cannot find an answer. For IRR, try giving a guess as the second argument, such as =IRR(B2:B10,10%).
#NULL! is rare. It appears when you separate two ranges with a space instead of a comma or colon, so Excel looks for the cells where they intersect and finds none. =SUM(A2:A10 C2:C10) should be =SUM(A2:A10,C2:C10).
#SPILL!: the result has nowhere to go
In Microsoft 365 and Excel 2021, formulas such as FILTER, SORT and UNIQUE can return many values that "spill" into neighbouring cells. If any of those cells already contain something, you see #SPILL!. Select the formula cell and Excel draws a dashed border around the area it needs. Clear or move whatever is in the way. Spill formulas also cannot be placed inside an Excel Table, so put them just outside it.
##### is not really an error
A cell full of hash signs simply means the column is too narrow to show the number or date. Double-click the right edge of the column heading to auto-fit it. If the hashes remain on a date or time, the value is negative, which often means a start time is later than an end time. Check the calculation or see our guide to date and time functions.
Circular reference warnings
A circular reference happens when a formula refers to its own cell, directly or through a chain. Putting =SUM(A2:A20) in A20 is the classic example. Excel warns you and usually returns zero. The status bar at the bottom-left shows the address of the circular cell; you can also use Formulas > Error Checking > Circular References. Change the range so the total sits outside the cells being added.
Tools for tracing errors
- Evaluate Formula (Formulas > Evaluate Formula) steps through a long formula one part at a time so you can see which piece fails.
- Trace Precedents and Trace Dependents draw arrows showing which cells feed a formula and which formulas use a cell.
- Error Checking walks through every error on the sheet in turn.
- Go To Special: press F5, click Special, choose Formulas and tick only Errors to select every error cell at once.
- Select part of a formula in the formula bar and press F9 to see its result. Press Esc afterwards so you do not replace the formula with the value.
Summary
- #N/A: lookup could not find the value; check spaces and number formats.
- #VALUE!: text where a number was expected.
- #REF!: a referenced cell was deleted.
- #DIV/0!: dividing by zero or blank; test the divisor first.
- #NAME?: typo, missing quotes or a function your version lacks.
- #SPILL!: clear the cells blocking a dynamic array result.
Back to: all Excel tutorials.