Microsoft Office Tutorials and References

In Depth Information

**Use Absolute References**

Figure 18-3:

Checking for

errors.

When there
are
errors, the dialog box appears and sticks around while you

work on each error. The Next and Previous buttons let you cycle through all

the errors before the dialog box closes. For each error it finds, you choose

what action to take:

✓
Help On This Error:
This leads to the Help system and displays the

topic for the particular type of error.

✓
Show Calculation Steps:
The Evaluate Formula dialog box opens, and

you can watch step by step how the formula is calculated. This lets you

identify the particular step that caused the error.

✓
Ignore Error:
Maybe Excel is wrong? Ignore the error.

✓
Edit in Formula Bar:
This is a quick way to just fix the formula yourself

if you don’t need any other help.

The Error Checking dialog box also has an Options button. Clicking the

button opens the Formulas tab from the Excel Options dialog box. In the

Formulas tab you can select settings and rules for how errors are recognized

and triggered.

Use Absolute References

If you are going to use the same formula for a bunch of cells, such as those

going down a column, the best method is to write the formula once and then

drag it down to the other cells by using the fill handle. The problem is that

when you drag the formula to new locations, any relative references change.

Often this
is the intention. When there is one column of data and an adjacent

column of formulas, typically each cell in the formula column refers to its

neighbor in the data column. But if the formulas all reference a cell that is

not adjacent, usually the intention is for all the formula cells to reference an