Microsoft Office Tutorials and References

In Depth Information

**Session 2.1**

2.
Select the range
C22:M24
, and then click the
Paste
button on the Standard toolbar.

Figure 2-5 shows the total expenses and net income values for each month in the Budget

worksheet. Note that Excel has duplicated the two formulas from the first month in each

succeeding month.

Figure 2-5

Copying and pasting a cell range

total monthly

expenses

total monthly

net income values

3.
Press the
Esc
key to remove the moving border from the selected range.

As you can see, Excel’s ability to adjust cell references when copying and pasting for-

mulas makes it easy to create columns or rows of formulas that share a common structure.

Using Relative and Absolute References

The type of cell reference that you just worked with is called a relative reference. A
relative

reference
is a cell reference that changes when it is copied and pasted in a new location.

Excel interprets the reference
relative
to the position of the active cell. For example, when

you copied the formula
=SUM(B6:B7)
from the source cell, B8, and pasted it in the desti-

nation range, C8:M8, Excel adjusted the cell references in each pasted formula relative to

the new location of the formula itself. The formula in cell C8 became
=SUM(C6:C7)
, the

formula in cell D8 became
=SUM(D6:D7),
and so on.

A second type of cell reference is an absolute reference. An
absolute reference
is a cell

reference that doesn’t change when it is copied. Excel does not adjust the cell reference

because the cell reference points to a fixed, or
absolute,
location in the worksheet, and it

remains fixed when the copied formula is pasted. In Excel, an absolute reference appears

with a dollar sign ($) before each column and row designation. For example, $B$8 is an

absolute reference, and when it is used in a formula, Excel will always point to the cell

located at the intersection of column B and row 8.

Figure 2-6 provides an example in which an absolute reference is necessary to a for-

mula. In this example, a sales worksheet records the units sold for each region as well as

the overall total. If you want to calculate the percent of units sold for each region, you

divide the units sold for each region by the overall total. If you use only relative references,

copying the formula from the first region to the second will produce an incorrect result,

because Excel shifts the location of the total sales cell down one row. To correct this prob-

lem, you use an absolute cell reference, fixing the location of the total sales cell at cell B8.

In the example, this means changing the formula in cell C4 from
=B4/B8
to
=B4/$B$8
.