3. Relative Cell References

Very early on in the development of spreadsheets it was quickly realised that entering formulas by hand was just too tedious for all but the simplest situtations.

There had to be an automatic way that the spreadsheet itself could generate formulas once a single formula had been entered by the user. And so the idea of Relative Cell Reference came about.

Let's use an example to explain this a little more simply:

Imagine you have a blank worksheet in front of you.  In cell A1 you type the number 1.  In cell A2 you type in the number 2 and continue until cell A5 where you type the number 5.  Your worksheet will look something like this:

Then in cell B1 you type =A1 and press the enter key

After pressing the 'enter' key, the formula that you wrote in B1 will pick up the value held in cell A1. It will look like this:

To copy the formula in B1 down to the other cells B2:B5, you would probably use the fill handle in B1 and drag down, so when you release the mouse button a set of extra formulas will have been entered automatically by the spreadsheet software.

The result would look like this:

The numbers in column B look exactly the same as those in column A.

However, if you were to look at the formulae in column B, they would look like this:

What happened when you dragged the formula down from B1 to B2 was that the spreadsheet understood that you were copying the formula down a row and that it needed to track that formula 'relative' to the cell one column to the left on the new row. So =A1 becomes =A2.

Advantages of relative cell references

- A formula can quickly be copied to a range of other cells

- When replicated, the relative cell reference will automatically update itself

Disadvantages of relative cell references

- Any mistake in a formula will be replicated to all other cells that are copied from the original formula

- If a formula is referencing just one cell, it will not stay in that cell once copied to other cells

- If a change is made to the original formula, it will need to be recopied to all the other cells

Exam hint

In an exam, you might be given a spreadsheet such as the example below and asked to describe how relative referencing is being used to calculate something such as the 'sales revenue'.

You should start off your answer by explaining how the 'sales revenue' formula is written, for example:

The sales revenue formula in cell D2 would be =B2*C2

Then explain that when the formula in D2 is replicated to D3:D5 by using the fill-handle, the formula is replicated from D2 and increments by one row each time it is dragged down to a new position.

Give an example to show that you fully understand what happens: the formula in D3 now becomes =B3*C3