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