Chapter 32: Making Your Worksheets Error-Free
653
Formulas returning an error
A formula may return any of the following error values:l (^) #DIV/0!
l #N/A
l (^) #NAME?
l #NULL!
l (^) #NUM!
l #REF!
l (^) #VALUE!
The following sections summarize possible problems that may cause these errors.
Tip
Excel allows you to choose how error values are printed. To access this feature, display the Page Setup dialog
box and click the Sheet tab. You can choose to print error values as displayed (the default), or as blank cells,
dashes, or #N/A. To display the Page Setup dialog box, click the dialog box launcher of the Page Layout ➪
Page Setup group. n
#DIV/0! errors
Division by zero is not a valid operation. If you create a formula that attempts to divide by zero,
Excel displays its familiar #DIV/0! error value.
Because Excel considers a blank cell to be zero, you also get this error if your formula divides by a
missing value. This problem is common when you create formulas for data that you haven’t
entered yet, as shown in Figure 32.2. The formula in cell D2, which was copied to the cells below
it, is
=(C2-B2)/C2
Often, an error in one cell is the result of an error in a precedent cell. For help in identifying the cell
causing an error value to appear, activate the cell that contains the error and then choose Formulas ➪
Formula Auditing ➪ Error Checking ➪ Trace Error. Excel draws arrows to indicate which cell is the
source of the error. After you identify the error, choose Formulas ➪ Formula Auditing ➪ Remove Arrows
to get rid of the arrow display.
Tracing Error Values