Deciphering Error Values in Excel 2013 Formulas - dummies

Deciphering Error Values in Excel 2013 Formulas

By Greg Harvey

Part of Excel 2013 For Dummies Cheat Sheet

You can tell right away that an Excel 2013 formula has gone haywire because instead of a nice calculated value, you get a strange, incomprehensible message. This weirdness is, in the parlance of Excel 2013 spreadsheets, an error value. Its purpose is to let you know that some element — either in the formula itself or in a cell referred to by the formula — is preventing Excel from returning the anticipated calculated value.

The following table lists some Excel 2013 error values and their most common causes.

What Shows Up in the Cell What’s Going On Here?
#DIV/0! Appears when the formula calls for division by a cell that
either contains the value 0 or, as is more often the case, is
empty. Division by zero is a no-no in mathematics.
#NAME? Appears when the formula refers to a range name that
doesn’t exist in the worksheet. This error value appears when you
type the wrong range name or fail to enclose in quotation marks
some text used in the formula, causing Excel to think that the text
refers to a range name.
#NULL! Appears most often when you insert a space (where you should
have used a comma) to separate cell references used as arguments
for functions.
#NUM! Appears when Excel encounters a problem with a number in the
formula, such as the wrong type of argument in an Excel function or
a calculation that produces a number too large or too small to be
represented in the worksheet.
#REF! Appears when Excel encounters an invalid cell reference, such
as when you delete a cell referred to in a formula or paste cells
over the cells referred to in a formula.
#VALUE! Appears when you use the wrong type of argument or operator in
a function, or when you call for a mathematical operation that
refers to cells that contain text entries.