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