Relative References

Introduction:

Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. This is a significant time-saver when creating spreadsheets with consistent calculations. It is possible to copy formulas and have the new formula appropriately update because of relative cell referencing.

Learning:

Relative Referencing is what you normally do in a spreadsheet. You’ve learned that cell references are defined by the Column and Row designation (i.e. A1) The reference A1 is called a relative cell reference. You’ve created formulas using these cell references. When a formula is copied across multiple cells, the formula’s cell references will change based on the relative position of rows and columns. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

How to Copy and Paste a Formula

When you copy a formula, Excel automatically changes the relative cell references for each new cell the formula is copied to. The picture below shows the steps.

Select cell B4, right-click, and then click Copy (or press CTRL and C) as shown below.

Next, select the cell C4 and right-click, and then click Paste under “Paste Options” (or press CTRL and v).

You can also drag the formula horizontally or vertically according to your data in the sheet. The picture below shows how you can select cell B4, click on the lower right corner of cell B4, and drag it across to cell C4. To drag, be sure to move your cursor to the lower right corner of the cell you wish to copy by using the fill handle  . and then drag the fill handle over the range that you want to fill. (Notice that this process is the same as you’ve done earlier with autofill.)

Explore the function that was copied from B4 to C4 now. This picture below shows the result: the formula in cell C4 references the values in column C. Because the cell references in the Formula in B4 were in relative cell reference, when the copied formula is pasted to column C (one column from column B) the cell references update to the same relative move, i.e. cells referencing column B will now reference column C. The new formula in C4 will be =C1*(C2+C3)

Relative cell references will update accordingly when copied to other rows. The following simple example shows a spreadsheet with 6 different product sales and costs, and a formula was created for the profit (Sales – Costs). The formula was created for the first product.

The process to copy the formula to the remaining rows is the same as earlier (you can use the autofill handle in the lower right corner of cell D2 and drag through D7 or you can use the Copy Paste menu commands) Each row reference in the formula will update accordingly. Notice in the picture below, after the formula in D2 was copied down the column, each rows formula is updated. The formula in D5 is now =B5-C5.

Moving a Formula

Moving a cell that contains a formula is different than copying the formula. When you move a formula, the cell references within the formula do not change no matter what type of cell reference that you use.

• Select the cell that contains the formula that you want to move.
• In the Clipboard group of the Home tab, click Cut.

Do one of the following:

• To paste the formula and any formatting: In the Clipboard group of the Home tab, click Paste.
• To paste the formula only: In the Clipboard group of the Home tab, click Paste, click Paste Special, and then click Formulas.

It’s important to be aware of the possibilities for how a relative cell reference might change when you move or copy a formula.

Summary:

Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

Sources:

Relative and Absolute Referencing. Authored by: Fran Wells. License: CC BY 4.0

Saylor Academy: PRDV0004:Spreadsheets. Retrieved from: https://learn.saylor.org/mod/page/view.php?id=26589 Licensed under: CC BY

IDM