Lesson created by instructor Francisco L. Gonzalez
 

Learning Objective: By the end of this lesson you will know what a relative cell reference is, and how relative references change when copied. A relative cell reference is a cell references that when copied, the copied reference is adjusted depending on the displacement of the copy.
 

  1. Every cell in an Excel spreadsheet has column-row location. This is the location of the cell within the spreadsheet.
  2. For example: cell A1 is located at column A and row 1.
  3. This column-row location is like an XY coordinate.
  4. You can copy the formula of a cell to another cell.
  5. Below, cell A1 has a formula written inside: = B1 + C1, as explained in the picture below.

Relative Addresses 1

Hints:

  • To see the formula inside a cell, click the cell press CTRL+` (Control key + Tilde). The picture below shows the effect of the CTRL+`

Relative Addresses 2

  • To go back to normal spreadsheet view click press CTRL+` again.

When we copy a formula from one cell to another is when we see the effect of the copied formula.

Now, analyze what happened when the formula inside cell A1 was copied to cell A7. Notice that the copy was in the same column, but to row 7. See picture below.

Relative Addresses 3

  1. A relative cell reference is a cell references that when copied, the copied reference is adjusted depending on the displacement of the copy.
  2. Because a reference refers to a cell or a range of cells, the formula results are automatically recalculated when the data is changed in the referenced cell(s).
  3. In regards to column, we see that the formula retains the same columns
  4. Because we copied to row 7, the formula has the row reference recalculated to 7 and now the formula is = B7 + C7
  5. Analyze what happened when the formula inside cell A1 was copied to cell B12. Now the copy has displacement on column and row.
  6. Notice that in regards to column, there is a displacement of one cell to the right, hence the formula has the columns bumped up by one column.

Excel will recalculate the formula that is being copied into cell B12. This recalculation will affect column and row, according to the displacements.

  1. The original formula inside A1 is: =B1+C1
  2. The copy is to cell B12. It has a displacement of 1 for the column and a displacement of 12 for the row.
  3. This mean that the copied formula will bump the column sides of the formula by 1 and the row sides of the formula by 12.
  4. This results in the formula copied to be = C12+D12

The visual below will help to see the displacement:

                               A1 is: =    B1    +    C1    is copied to cell B12

Relative Addresses 4

Key Learning:
 
When relative cell references are copied from one cell to another, the formulas are updated to reflect the displacement in columns and rows.

———————————————————————————————————————–
Copyright: Microsoft Windows® and Microsoft Excel® are trademarks of Microsoft Corporation.