Microsoft Office Tutorials and References
In Depth Information
Using cell references in formulas
How copying affects cell references
One of the handiest benefits of using references is the ability to copy and paste formulas.
But you need to understand what happens to your references after you paste so that you
can create formulas with references that operate the way you want them to operate.
Copying relative references When you copy a cell containing a formula with relative
cell references, Excel changes the references automatically relative to the position of the
cell where you paste the formula. Referring to Figure 12-2, suppose you type the formula
=AVERAGE(B4:E4) in cell F4. This formula averages the values in columns B through E.
Figure 12-2 Cell F4 contains relative references to the cells to its left.
You’ll find the Exams.xlsx file with the other examples on the companion website.
You want to include this calculation for the remaining rows as well. Instead of typing a new
formula in each cell in column F, select cell F4 and press Ctrl+C to copy it (or click the Copy
button in the Clipboard group on the Home tab). Then select cells F5:F8, click the arrow
next to the Paste button on the Home tab, click Paste Special, and then select the
Formulas And Number Formats option (to preserve the cell and border formatting). Figure 12-3
shows the results. Because the formula in cell F4 contains a relative reference, Excel adjusts
the references in each copy of the formula. As a result, each copy of the formula calculates
the average of the cells in the corresponding row. For example, cell F5 contains the formula