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

off.

Expanding Cells

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.

Tip

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.”