4. Absolute Cell References

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:

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 fill-handle in cell E2 and drag the formula down to cells E3:E5. However, when this is done, the following results happen:

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:

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

The results from using an absolute cell reference in this table would be:

The example above looks at dragging a formula vertically down. However, you can also use absolute cell references horizontally. Look at the following example:

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

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'.

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 fill-handle, 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.