Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
Formulas returning an error
A formula may return any of the following error values:
The following sections summarize possible problems that may cause these errors.
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 in the Page Layout➜Page Setup group.
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 21-2. The formula in cell D2, which was copied to the cells below
it, is as follows:
Tracing error values
Often, an error in one cell is the result of an error in a precedent cell (a cell that is used by the
formula). To help track down the source of an error value in a cell, select the cell and choose
Formulas➜Formula Auditing➜Error Checking➜Trace Error. Excel draws arrows to indicate the
After you identify the error, use Formulas➜Formula Auditing➜Error Checking➜Remove Arrows
to get rid of the arrow display.