Microsoft Office Tutorials and References
In Depth Information
Understanding How Excel Handles Formula Errors
To hide formulas once again, click the Show
Formulas button on the Format tab to turn it
Don’t freak out when Excel expands the
result cells in order to display your formulas.
Although the worksheet may look a bit odd,
result cells will return to their normal size
when you turn off the display of formulas.
To quickly display or hide formulas
in the current worksheet, press Ctrl+`
(the accent key to the left of the 1 key—
it also has a tilde ~ on it).
As a reminder, while you are poking around the
worksheet checking formulas, keep in mind that
if you click a result cell, the cells referred to
in that formula are highlighted by colored boxes
that match the same cell addresses in the
worksheet (see Figure 4-1). Remember as well that you
can drag a colored box and drop it on a new cell
to change a cell reference used in the formula.
Handles Formula Errors
Granted, Excel can’t read your mind.
So it can’t know that you meant to subtract the
July Expenses from July Revenues. Thus, it won’t
bop you on the head when you accidentally
subtract June’s Expenses from July Revenues
instead. Still, there are some errors Excel knows
are errors, such as dividing by zero. Excel helps
you to identify most of these errors when they
occur by displaying an error message such as
#DIV/0 in the result cell of the formula
containing the error. For other errors such as forgetting
to type a closing parenthesis, Excel may flash
a dialog box at you indicating the mistake
(and offering you a chance right then to fix it),
and not display an error message in the cell.
All of these errors are described in detail in the
section “Understanding Formula Error Messages.”
Because some formula errors do not cause an
error message to display in the result cell, you
may want Excel to help you identify errors in
another way—through an instant, “Hey, that’s an
error!” flagging thing, or with a “Let’s check the
worksheet for errors now, one at a time, just like
a spelling checker” sort of thing. You learn both
methods in the section “Checking for Errors
Automatically One at a Time.”