Jan's Working with Numbers:

Error Values


Home > Jan's CompLit 101 > Working with Numbers > Appendix

Excel puts error values in a cell to tell you that something is wrong.

In Project 3: Format and Arrange, the error values  ###### and #REF! are discussed.
There are several other errors that you may run into from time to time.

Error Value Cause Solution

#####

Column is too narrow to show the whole number

Widen the column.

#REF!

Cell reference in formula is not valid because you deleted the cell or pasted something else over it.

Rewrite the formula.

#VALUE!

A formula expects a number but the cell you name has only text in it. For example the formula =B3+B4 would give this error if B4 contains text.

Rewrite your formula to refer to the correct cells or use a function that will ignore text values, like
= SUM(B3:B4).

#N/A

A value you used in a formula or as an argument for a function is "Not Available" now. You can enter this expression in a cell yourself to show that you haven't entered data yet.

Do the calculation needed to get the values.

#DIV/0!

Your formula tried to divide with a cell which is blank, contains text, or has value of 0.

Change the cell reference or enter a non-zero value in the cell to divide with. If the cell contains #N/A (for Not Available), then the formula will give #N/A as the answer, too.

#NAME?

You used a name for a range or a function that Excel does not understand. Perhaps you misspelled it or left out the colon in a range reference.

Correct spelling. Include the colon in all ranges. Make sure the name or function exists.

#NUM!

There is a problem with a number in a formula or function. It may be too large or small for Excel to handle. It might be the wrong type for a function, like text instead of a number.

Check for wrong type of arguments. Revise the formula or change your whole strategy if the answers will not fit in Excel.

#NULL!

You referred to the intersection of two data regions which do not really intersect.

Correct your formula. Possibly use the union of two regions that do not intersect instead.


Home > Jan's CompLit 101 > Working with Numbers > Appendix

Last updated: September 17, 2012