4. Absolute Cell References
DEFINITION: When a formula is replicated to other cells, any part of the formula which contains an absolute cell reference will remain static or unmodified when the formula/function is copied. 
We saw on the previous page that with a relative cell reference, the formula will change automatically as it is copied to other cells.
However, sometimes you might want the cell that the formula is referencing not to change as it is dragged to different cells.
Have a look at the following example:
A 
B 
C 
D 
E 
F 

1 
Item 
Sale Price 
Number sold 
Sales Revenue 
Discount 
Discount value 
2 
8" plate 
£6.99 
5 
£34.95 
£3.49 
10% 
3 
10" plate 
£8.99 
9 
£80.91 

4 
Mug 
£5.99 
4 
£23.96 

5 
Teacup 
£5.99 
3 
£17.97 
The shop has decided to offer a discount of 10% during January on all sales.
The 10% discount value has been placed in cell F2.
In cell E2 a formula calculates the discount by using the following formula: =D2*F2
The discount is calculated to be £3.49
The next logical step would be to use the fillhandle in cell E2 and drag the formula down to cells E3:E5. However, when this is done, the following results happen:
A 
B 
C 
D 
E 
F 

1 
Item 
Sale Price 
Number sold 
Sales Revenue 
Discount 
Discount value 
2 
8" plate 
£6.99 
5 
£34.95 
£3.49 
10% 
3 
10" plate 
£8.99 
9 
£80.91 
0 

4 
Mug 
£5.99 
4 
£23.96 
0 

5 
Teacup 
£5.99 
3 
£17.97 
0 
By looking at the results in column E, you can see straight away that something is wrong, but you have to take a closer look at the formulae to see what has happened. These are the formulae:
A 
B 
C 
D 
E 
F 

1 
Item 
Sale Price 
Number sold 
Sales Revenue 
Discount 
Discount value 
2 
8" plate 
£6.99 
5 
£34.95 
=D2*F2 
10% 
3 
10" plate 
£8.99 
9 
£80.91 
=D3*F3 

4 
Mug 
£5.99 
4 
£23.96 
=D4*F4 

5 
Teacup 
£5.99 
3 
£17.97 
=D5*F5 
The original formula in cell E2 works correctly, it is multiplying D2 by the discount value in F2.
However, when it is dragged down, relative cell referencing changes both values. The first part of the formula changes correctly, D2 becomes D3, D3 becomes D4.
But the second part of the formula also changed relatively and it now doesn't do what we want. F2 became F3. If you have a look at F3, nothing is contained in that cell. So now we have D3 multiplied by F3 (nothing), so we get the result of 0.
To overcome this problem, we to allow the first part of the formula to change relatively but we stop the second part from changing. In other words, we make it 'absolute'.
To make part of a formula into an 'absolute cell reference', a $ symbol is used. The $ tells the spreadsheet not to alter the formula as you drag or copy it to another cell.
If you are dragging the formula down, then the $ needs to go in front of the row number.
So, in our example, the formula in cell E3 would become =D2*F$2
A 
B 
C 
D 
E 
F 

1 
Item 
Sale Price 
Number sold 
Sales Revenue 
Discount 
Discount value 
2 
8" plate 
£6.99 
5 
£34.95 
=D2*F$2 
10% 
3 
10" plate 
£8.99 
9 
£80.91 
=D3*F$2 

4 
Mug 
£5.99 
4 
£23.96 
=D4*F$2 

5 
Teacup 
£5.99 
3 
£17.97 
=D5*F$2 
The results from using an absolute cell reference in this table would be:
A 
B 
C 
D 
E 
F 

1 
Item 
Sale Price 
Number sold 
Sales Revenue 
Discount 
Discount value 
2 
8" plate 
£6.99 
5 
£34.95 
£3.49 
10% 
3 
10" plate 
£8.99 
9 
£80.91 
£8.09 

4 
Mug 
£5.99 
4 
£23.96 
£2.39 

5 
Teacup 
£5.99 
3 
£17.97 
£1.79 
The example above looks at dragging a formula vertically down. However, you can also use absolute cell references horizontally. Look at the following example:
A 
B 
C 
D 
E 
F 

1 
Sales person 
Emma 
Sam 
James 

2 
Weekly Sales 
£525 
£750 
£490 

3 
Commission Earned 
£5.25 
£7.50 
£4.90 

4 

5 
Commission Rate 
1% 
The commission earned was calculated by writing the following formula in cell B3:
=B2*$B5
When this formula was dragged horizontally from B3 to C3, the first part changed from =B2 to =C2
However, the $ in front of the second B in the formula, stopped the column letter from changing, it made it absolute. The other two formulae became:
=C2*$B5 and =D2*$B5
A 
B 
C 
D 
E 
F 

1 
Sales person 
Emma 
Sam 
James 

2 
Weekly Sales 
£525 
£750 
£490 

3 
Commission Earned 
=B2*$B5 
=C2*$B5 
=D2*$B5 

4 

5 
Commission Rate 
1% 
Hint
Putting the $ in front of the row number or column letter only is technically the correct way to write an absolute cell reference. However, if you can't remember the correct way to write one, it is very unlikely that you would be penalised by putting a $ in front of both:
=C2*$B$5
Advantages of absolute cell references
 the 'absolute' part of the formula will not change even if copied elsewhere
 Just one cell needs to be changed in order to update all of the cells related with a formula
Disadvantages of absolute cell references
 If a range of cells are using a formula which has an absolute cell reference, a change will affect all cells. You might not want this to happen.
 it is more complicated to enter an absolute cell reference as you have to be careful about the placement of the dollar signs.
Exam hint
In an exam, you might be given a spreadsheet such as the example below and asked to describe how absolute referencing is being used to calculate the 'discount to be given'.
A 
B 
C 
D 
E 
F 

1 
Item 
Sale Price 
Number sold 
Sales Revenue 
Discount to be given 
Discount 
2 
8" plate 
£6.99 
5 
£34.95 
£3.49 
10% 
3 
10" plate 
£8.99 
9 
£80.91 
£8.09 

4 
Mug 
£5.99 
4 
£23.96 
£2.39 

5 
Teacup 
£5.99 
3 
£17.97 
£1.79 
You should start off your answer by explaining how the 'discount to be given' formula is written, for example:
The discount to be given formula in cell E2 would be =D2*F$2
Then explain that when the formula in E2 is replicated to E3:E5 by using the fillhandle, the part of the formula in E2 is replicated and incremented. However, the part of the formula, F$2 will remain absolute (exactly the same) when the formula is replicated.
challenge see if you can find out one extra fact on this topic that we haven't already told you
Click on this link: Absolute Cell Reference