Microsoft Office/Troubleshooting Excel Errors

From Wikibooks, open books for an open world
Jump to: navigation, search

Common sources of errors are parentheses that don’t match or missing arguments for functions. If your formula is free from those errors, here are some error values you might get:

Error Description
###### There is nothing wrong with your formula; the cell simply isn’t big enough to display the result. Widen the column. or set "Shrink to fit" under Alignment.
#DIV/0 You are trying to divide by zero
  • Correct the divisor
  • If the divisor is a cell reference, check to make sure the cell isn’t empty
#NAME? There is a name in the formula that Excel doesn’t recognize.
  • If you used a natural language name, check the spelling
  • If you typed in a function, check the spelling or verify that the function exists.
  • If you are performing operations on text, enclose the text in double quotation marks
#REF! A cell reference is not valid. Reenter the formula.
#VALUE! The formula uses the wrong type of operand or argument. Check to see that you’re not performing math operations on labels or that arguments of functions that need to numeric are not referring to cells containing labels.

A handy function to avoid errors is ISERR. For example, to divide one column by another, where blanks and zeros are present, use this formula (in cell C1): =IF(ISERR(A1/B1),"",A1/B1)