Excel error values: how to fix #N/A, #VALUE!, #REF! and more
An error value is Excel telling you why a formula cannot calculate. Once you can read the eight common ones, most fixes take a minute: #N/A usually means the lookup is searching the wrong column, #NAME? usually means a typo.
In short: #N/A means "not found", #VALUE! "wrong type of data", #DIV/0! "divided by zero", #REF! "that cell no longer exists" and #NAME? "Excel does not know this name". Fix the cause first, then catch only the errors you expect with IFERROR.
Try it now: fix a #N/A
The item number exists, yet the VLOOKUP returns #N/A. The broken formula is in the task. Write the working one in the yellow cell.
Task F2 contained =VLOOKUP(E2,B2:C8,2,FALSE) and Excel showed #N/A, although the item number in E2 does exist. Write a VLOOKUP in F2 that finds the price for the item number.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item no. | Item | Price $ | Item no. | Price $ | |
| 2 | K-208 | Wide binder | 4.9 | K-276 | ||
| 3 | K-115 | Hole punch | 12.5 | |||
| 4 | K-342 | Notepad A5 | 3.2 | |||
| 5 | K-097 | Desk lamp | 27.9 | |||
| 6 | K-276 | Highlighters 4 pack | 8.4 | |||
| 7 | K-151 | Stapler | 15.75 | |||
| 8 | K-319 | Sticky notes | 6.3 |
Every error value at a glance
| Error | German Excel | Meaning | Most common cause |
|---|---|---|---|
| #N/A | #NV | Value not available | Lookup value not found |
| #VALUE! | #WERT! | Wrong data type | Text inside a calculation |
| #DIV/0! | #DIV/0! | Division by zero | Divisor is 0 or blank |
| #REF! | #BEZUG! | Invalid reference | Row or column deleted |
| #NAME? | #NAME? | Unknown name | Misspelled function |
| #NUM! | #ZAHL! | Invalid number | Calculation is mathematically impossible |
| #SPILL! | #ÜBERLAUF! | Result has no room | Cells in the spill range are not empty |
| #NULL! | #NULL! | Empty intersection | A space instead of a comma between two ranges |
| #### | #### | Not really an error | Column too narrow or negative date |
#N/A: value not available
#N/A nearly always comes from a lookup such as VLOOKUP, XLOOKUP or MATCH. Work through these causes:
- The lookup value is not in the first column of the table, as in the exercise above.
- Hidden spaces: "Boston " is not "Boston". TRIM removes them.
- Number versus text: 1042 as a number will not match "1042" stored as text. A small green triangle in the cell flags numbers stored as text.
- The value really is missing. Then #N/A is the correct answer, and IFNA or IFERROR can display it more politely.
#VALUE!: text where a number should be
Operators like + and * need numbers. If a cell contains "n/a", a single space, or a date that Excel stored as text, the calculation returns #VALUE!. Functions like SUM skip text inside a range instead:
Task The figures come from an export. Munich has not reported yet, so B4 says “n/a”. B8 contained =B2+B3+B4+B5+B6+B7 and Excel showed #VALUE!. Write a formula in B8 that still adds up the orders that were reported.
| A | B | |
|---|---|---|
| 1 | Branch | Orders |
| 2 | Berlin | 128 |
| 3 | Hamburg | 94 |
| 4 | Munich | n/a |
| 5 | Cologne | 152 |
| 6 | Leipzig | 87 |
| 7 | Dresden | 116 |
| 8 | Total |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
If the number itself was imported as text, convert it with =VALUE(A2) or the warning icon next to the cell.
#DIV/0!: division by zero
The divisor is 0 or blank. Ratios such as =B2/C2 show it until C2 is filled in. AVERAGE returns it too when the range contains no numbers at all. If an empty divisor is normal, handle it: =IF(C2=0,"",B2/C2).
#REF!: the reference points nowhere
A row, column or sheet the formula used has been deleted. Excel replaces the reference with #REF!, so you see something like =SUM(#REF!). Undo with Ctrl+Z is the quickest fix, then adjust the formula. You also get #REF! when a VLOOKUP column index is larger than the number of columns in the table.
#NAME?: Excel does not recognise a name
Usually a typo in the function name. Other causes: text without quotes, as in =IF(A2=Yes,…), a named range that does not exist, or a function your version of Excel does not have.
Task B9 contained =AVERGE(B2:B7) and Excel showed #NAME?. Write the correct formula in B9 so it calculates the average travel costs.
| A | B | |
|---|---|---|
| 1 | Employee | Travel costs $ |
| 2 | Jana Weber | 412.5 |
| 3 | Murat Kaya | 238 |
| 4 | Lena Hoffmann | 690.4 |
| 5 | Tom Schulz | 155 |
| 6 | Sara Klein | 503.2 |
| 7 | Ali Demir | 321 |
| 8 | ||
| 9 | Average |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
#NUM!, #SPILL!, #NULL! and ####
#NUM!
The calculation cannot be done, for example =SQRT(-4), a result that is too large, or a k bigger than the list in LARGE and SMALL.
#SPILL!
A formula like FILTER or SORT wants to write its result into several cells, but something is already in the way. Clear the cells below the formula and the result appears. Spilling formulas also do not work inside a formatted Excel table (Ctrl+T). See FILTER, SORT and UNIQUE.
#NULL!
Rare but confusing: a space between two ranges is Excel's intersection operator. =SUM(A1:A5 C1:C5) has no cell in common. A comma was intended.
####
The column is too narrow for the number or date. Double-click the right edge of the column header to fit it. If #### stays even in a wide column, a date or time is negative.
Find the cause faster
- Evaluate Formula: Under Formulas > Formula Auditing, Excel walks through the formula step by step and shows which part produces the error.
- F9 in the formula bar: Select part of a formula and press F9 to see its result. Press Esc afterwards, or the value replaces that part of the formula.
- Select every error: Home > Find & Select > Go To Special > Formulas, with only "Errors" ticked.
Fix it or catch it?
An error value is a signal, not a flaw. If the formula is wrong, fix the cause. If the error is an expected state, like a new item number that is not in the price list yet, catch it with IFERROR or IFNA. Catching everything by default hides real problems later, such as a deleted sheet.
Error values in German Excel
The German names are in the table above. Excel translates error values and functions automatically, so a workbook made in German Excel shows #N/A and VLOOKUP when you open it in English Excel. If a German colleague mentions #WERT! or #BEZUG!, they mean #VALUE! and #REF!.
Which Excel versions have which errors?
#N/A, #VALUE!, #DIV/0!, #REF!, #NAME?, #NUM! and #NULL! exist in every version. #SPILL! and #CALC! only appear in versions with dynamic arrays, which means Microsoft 365 and Excel 2021 or later. Open a file that uses a newer function like XLOOKUP in an older version and you get #NAME?, because that function is missing.
FAQ
What does #N/A mean in Excel?
It stands for "not available": a lookup function could not find the lookup value. Check the lookup column, extra spaces and whether numbers and text match.
Why does Excel show #VALUE! instead of a total?
The calculation contains text, often a cell with a space or a note like "n/a". Use SUM instead of plus signs, or fix the cell.
How do I count the errors in a range?
Use =SUMPRODUCT(--ISERROR(A2:A100)). ISERROR returns TRUE or FALSE for each cell, and the double minus turns those into 1 and 0.
How do I sum a range that contains errors?
SUM stops at the first error. AGGREGATE can skip them: =AGGREGATE(9,6,B2:B20), where 9 means sum and 6 means ignore error values.