Microsoft Office Tutorials and References

In Depth Information

**Using Absolute References**

Figure 3-3

Formulas using a relative reference

formula references

a cell three rows up

and three columns

to the left of the

active cell

when copied to new

cells, each formula

still references a

cell three rows up

and two columns to

the left

values returned by

each formula

In this ﬁ gure, the formula =A2 entered in cell D5 displays the value of 10, which is

the value entered in cell A2. When pasted to a new location, each of the pasted

formulas contains a reference to a cell that is three rows up and three rows to the left of the

current cell’s location. One of the great advantages of relative references is that you can

quickly generate row and column totals without having to worry about revising the

formulas as you copy them to new locations.

Using Absolute References

A good practice when designing a workbook is to enter values in their own cells in one

location of the worksheet, and then reference the appropriate cells in formulas

throughout the worksheets. This reduces the amount of data entry when you need to use the

same data in more than one location. It also makes it faster and more accurate when you

need to change a data value, as all the formulas based on that cell are updated to reﬂ ect

the new value.

Next, you will enter the Drakes’ monthly income. The couple’s income changes in

the summer as Diane works fewer hours and Glenn works more. Rather than inserting

these same values each month, you’ll enter them in cells at the top of the worksheet and

then reference those values in the income/expenses table at the bottom of the worksheet.

Later, if Diane modiﬁ es the monthly income estimates, you’ll need to change them in

only one location rather than in 12 different locations.