Microsoft Office Tutorials and References

In Depth Information

**Copying Formulas with AutoFill**

Relatively speaking

Figure 4-6 shows the worksheet after the formula in a cell is copied to the cell

range C12:E12 and cell B12 is active. Notice how Excel handles the copying of

formulas. The original formula in cell B12 is as follows:

=SUM(B3:B11)

When the original formula is copied to cell C12, Excel changes the formula

slightly so that it looks like this:

=SUM(C3:C11)

Excel adjusts the column reference, changing it from B to C, because I copied

from left to right across the rows.

When you copy a formula to a cell range that extends down the rows, Excel

adjusts the row numbers in the copied formulas rather than the column

letters to suit the position of each copy. For example, cell E3 in the Mother

Goose Enterprises – 2013 Sales worksheet contains the following formula:

=SUM(B3:D3)

When you copy this formula to cell E4, Excel changes the copy of the formula

to the following:

=SUM(B4:D4)

Excel adjusts the row reference to keep current with the new row 4 position.

Because Excel adjusts the cell references in copies of a formula relative to

the direction of the copying, the cell references are known as
relative cell

references
.

Some things are absolutes!

All new formulas you create naturally contain relative cell references unless

you say otherwise. Because most copies you make of formulas require

adjustments of their cell references, you rarely have to give this arrangement a

second thought. Then, every once in a while, you come across an exception

that calls for limiting when and how cell references are adjusted in copies.

One of the most common of these exceptions is when you want to compare

a range of different values with a single value. This happens most often when

you want to compute what percentage each part is to the total. For example, in