Microsoft Office Tutorials and References

In Depth Information

**Detecting and Correcting Errors in Formulas**

This is the same AutoFill handle you drag to enter serial data (see

Chapter 1 of this mini-book about entering lists and serial data with the

AutoFill command). The AutoFill handle is the small green square in the

lower-right corner of the cell. When you move the mouse pointer over it,

it changes to a black cross. Figure 3-9 shows a formula being copied.

3.
Release the mouse button.

If I were you, I would click in the cells to which you copied the formula

and glance at the Formula bar to make sure that the formula was copied

correctly. I’d bet you it was.

You can also copy formulas with the Copy and Paste commands. Just make

sure that cell references refer correctly to the surrounding cells.

Detecting and Correcting Errors in Formulas

It happens. Everyone makes an error from time to time when entering

formulas in cells. Especially in a worksheet in which formula results are calculated

into other formulas, a single error in one formula can spread like a virus

and cause miscalculations throughout a worksheet. To prevent that

calamity, Excel offers several ways to correct errors in formulas. You can correct

them one at a time, run the error checker, and trace cell references, as the

following pages explain.

By the way, if you want to see formulas in cells instead of formula results, go

to the Formulas tab and click the Show Formulas button or press Ctrl+’

(apostrophe). Sometimes seeing formulas this way helps to detect formula

errors.

Correcting errors one at a time

When Excel detects what it thinks is a formula that has been entered

incorrectly, a small green triangle appears in the upper-left corner of the cell

where you entered the formula. And if the error is especially egregious, an

error message,
a cryptic three- or four-letter display preceded by a pound

sign (#), appears in the cell. Table 3-2 explains common error messages.