Microsoft Office Tutorials and References

In Depth Information

**Using Absolute References**

Figure 3-6

Formulas using an absolute reference

formula containing

an absolute

reference to the sales

tax rate in cell A2

absolute reference

to cell A2

relative references

to cells C2 and D2

when pasted into a

new location, the

absolute reference

remains unchanged

$A$2 continues to

be referenced in

the formula

relative references

change based on

cell location

values returned by

the cell formulas

In this ﬁ gure, the sales tax of different purchases is calculated and displayed. All items

have the same 5 percent tax rate applied to the purchase, with the tax rate stored in

cell A2. The sales tax and total cost of the ﬁ rst item are calculated in cells D2 and E2,

respectively. When those formulas are copied and pasted to the remaining purchases,

the relative references in the formulas are modiﬁ ed to point to the new location of the

purchase cost; however, the sales tax rate continues to point to cell A2, regardless of the

location of the selected cell.

You will modify the formulas in the Budget worksheet so that they reference Diane’s

income estimates using absolute rather than relative references.

To use absolute references to display the monthly income:

◗

1.
In cell C19, enter
=$D$5
. This formula contains an absolute reference to cell D5,

which contains Diane’s monthly income during the school months.

◗

2.
In cell C20, enter
=$D$6
. This formula contains an absolute reference to cell D6,

which contains Glenn’s monthly income during the school months.

◗

3.
Copy the corrected formulas in the range
C19:C20
, and then paste them in the

range
D19:G20
. As shown in Figure 3-7, the months of February through May now

correctly show the monthly income values for the school months.