Chapter 19: Making Your Formulas Error-Free
19
Excel allows you to choose the way error values are printed. To access this feature, display the Page Setup dialog box
and select 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.
Tracing Error Values
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.#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.To avoid the error display, you can use an IF function to check for a blank cell. For
instance, this formula will display an empty string if cell B4 is blank or contains 0 ; other-
wise, it displays the calculated value:
=IF(B4=0,"",C4/B4)Another approach is to use an IFERROR function to check for any error condition. The fol-
lowing formula, for example, displays an empty string if the formula results in any type of
error:
=IFERROR(C4/B4,"")The IFERROR function was introduced in Excel 2007. For compatibility with previous versions of Excel, use this
formula:
=IF(ISERROR(C4/B4),"",C4/B4)