Module 3.1
Task 6 - Relative and Absolute Referencing


A.M.D.G.

A similar formula is sometimes needed all the way down a column of a spreadsheet. To do this we replicate or fill the formula down the required cells.

Think about the following spreadsheet:
A
B
C
D
1
 Name Paper 1 Paper 2 Total Score
2
 Eddie 46 37 = B2 + C2
3
 Chris 38 40  
4
 Martin 41 49  

What would appear in cells D3 and D4 if you filled down the formula in cell D2?

Obviously we don't want =B2+C2 to appear in every cell. The computer automatically changes the cell references to =B3+C3 and =B4+C4. This is called Relative Referencing.

Sometimes we don't want this to happen. In these cases we would use an absolute reference.

For example:

 
A
B
C
1
 Exam out of 45  
2
 Name Exam Result Percentage
3
 Eddie 23 = B3 / B$1 * 100
4
 Chris 34  
Martin 41  

All the formula have to refer to cell B1 so this has not updated. It has to be an absolute reference. To indicate to the computer that you do not want B1 to change you must enter the reference as B$1. If you were filling across you would need to change the reference to $B1.

  • Enter the following information in a new spreadsheet document
 
A
B
C
D
1
 Number of Pupils 3    
2
   Height in 2001 Height in 2002 Growth
3

Pupil 1

 1.45 1.53  
4
 Pupil 2 1.52 1.67  
5
 Pupil 3 1.38 1.44  
6
 Average      
  • Enter a formula in cell D3 to calculate the growth for pupil 1
  • Fill down this formula to cells D4 and D5
  • Enter a formula in cell B6, using the value in cell B1, to calculate the average height in 2001
  • Fill this formula across to C6 and D6, ensuring Absolute Referencing is used