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

=AVERAGE(B5:E5).