Print this chapterPrint this chapter

Formulas, Relative References, Order of Operations, and Auditing Formulas

Relative References (Copying and Pasting Formulas)

Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, Figure 4 Formula Output for Monthly Spend shows the output of the formula that was entered into cell C3. However, this calculation needs to be performed for the rest of the cell locations in Column C. Since we used the D3 cell reference in the formula, Excel automatically adjusts that cell reference when the formula is copied and pasted into the rest of the cell locations in the column. This is called relative referencing and is demonstrated as follows:

  1. Click cell C3.
  2. Click the Copy button in the Home tab of the Ribbon.
  3. Highlight the range C4:C11.
  4. Click the Paste button in the Home tab of the Ribbon.
  5. Double click cell C6. Notice that the cell reference in the formula is automatically changed to D6.
  6. Press the ENTER key.

Figure 5 Relative Reference Example shows the outputs added to the rest of the cell locations in the Monthly Spend column. For each row, the formula takes the value in the Annual Spend column and divides it by 12. You will also see that cell D6 has been double clicked to show the formula. Notice that Excel automatically changed the original cell reference of D3 to D6. This is the result of relative referencing, which means Excel automatically adjusts a cell reference relative to its original location when it is pasted into new cell locations. In this example, the formula was pasted into eight cell locations below the original cell location. As a result, Excel increased the row number of the original cell reference by a value of one for each row it was pasted into.

Relative Reference Example

Figure 5 Relative Reference Example


Why?

Use Relative Referencing

Relative referencing is a convenient feature in Excel. When you use cell references in a formula, Excel automatically adjusts the cell references when the formula is pasted into new cell locations. If this feature were not available, you would have to manually retype the formula when you want the same calculation applied to other cell locations in a column or row.