Relative vs. Absolute Addressing in a Spreadsheet

1
Relative vs. Absolute Addressing in a Spreadsheet

• Spreadsheet Address
• Relative Address
• Absolute Address
• Examples

2
Relative Address

• you can copy formulas as well as text or numeric
  data - what happens to the cell reference
  (address)
• when the formula is copied, the cell references
  adjust to their new locations because a formula
  automatically uses a relative cell reference

3
Example of Copying a Formula with Relative
Addressing

• suppose B7 contains the formula
• B5B6
• this formula (cell) is copied into C7
• the copied formula in C7 is
• C5 C6
• in relative terms the formula says use the cells
  one row up and two rows up

4
Example of Copying a Formula with Relative
Addressing

• suppose B7 contains the formula
• C6
• this formula (cell) is copied into C7
• the copied formula in C7 is
• D6
• in relative terms the address is one column to
  the right and one row up

5
Relative address (reference)

• the cell address in the formula changes to
  reflect the relative position
• relative is default
• be careful when deleting, you could affect
  formulas referencing the deleted cells

6
Example 1
The formula in A4 is A2 A3 Fill A4 to B4 B4
_________________ Copy A4 to C5 C5
_________________
7
Answer to Example 1

• B4 B2 B3
• C5 C3 C4

8
Absolute Addressing

• no adjustment takes place
• the position is fixed
• since relative is default, Excel must allow you
  to indicate that an address is absolute. This is
  done by placing a in front of the coordinate
  you want fixed.

9
Example of Copying Formula with Absolute
Addressing

• suppose B7 contains the formula
• A1
• this formula (cell) is copied into C7
• the copied formula in C7 is
• A1
• the formula says the position of the column is
  absolute and position of the row is absolute

10
Example of Copying Formula with Absolute
Addressing

• suppose B7 contains the formula
• A1
• this formula (cell) is copied into C8
• the copied formula in C8 is
• A2
• the formula says the position of the column is
  absolute and position of the row is six rows up

11
Example 2
The formula in A4 is A2 D3 Fill A4 to B4 B4
_____________________ Copy A4 to C5 C5
_____________________
12
Answer to Example 2

• B4 B2 D3
• C5 C3 D3

13
Additional Examples

• The formula in A3 is A2/A4
• Copy A3 to C3 C3_____________
• Copy A3 to D2 D2_____________
• The formula in A3 is A2/A4
• Copy A3 to C3 C3_____________
• Copy A3 to D4 D4_____________

14
Answers to Additional Examples

• The formula in A3 is A2/A4
• Copy A3 to C3 C3 C2/A4
• Copy A3 to D2 D2 D1/A3
• The formula in A3 is A2/A4
• Copy A3 to C3 C3 C2/C4
• Copy A3 to D4 D4 D3/D4

15
Questions