Microsoft Office Tutorials and References
In Depth Information
Copying Formulas with AutoFill
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:
When the original formula is copied to cell C12, Excel changes the formula
slightly so that it looks like this:
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:
When you copy this formula to cell E4, Excel changes the copy of the formula
to the following:
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
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