Intro

A Core capability of Excel is its use for calculations. There are a few basic principles that will make your life easier if you follow them. One of the fundamentals is the ability to do calculations by refering to the values in other cells. Relative and Absolute references make this a powerful and efficient way of building calculations - see below:

Note - Copying and pasting formulae

You will need to do this in the following example - you can do this in several ways - see Keyboarding for examples. My preferred option is CTRL+C to copy and CTRL+V to paste.

Relative and absolute references

One of the powerful things about Excel is the abilty to copy a formula from one cell to another (or multiple others) and have it repeat the calculation (formula) you built. This is useful for tables of data or where you have time periods, departments or any other groups of data which all require the same calculation to be done.

This task is made easier by relative and absolute referencing. Imagine trying to create a grid of the times tables up to 12x12; you would need 24 'input' or 'data' cells - one set of 12 cells on the top row and one vertically down the left hand column, with each value from 1 to 12. The square formed by these two 'data sets' has 144 intersections, represented by the cells inside the square. To fill in the times tables, how many formulae do you need? a)144 b)12 c)1

The correct answer is c - only one formula is needed. This nicely illustrates the power of absolute references.

if cells A2 to A13 contain the numbers 1 to 12 and B1 to M1 also contain the numbers 1 to 12, the cells in the square formed are cells B2 to M13.

The formula in cell B2 could be =B1*A2 that would give us the correct answer, but if we copy it across to M2 we get

479,001,600 - hmmm - not quite what we want. These references are relative - they are looking at the position of the referenced cells (called precedents) relative to the active cell (the one we are building our formula in). The consequence in our example is that each formula in B2 to M12 multiplies the cell to its left by the cell above it.

What we really want to do is to always multiply the cell in Column A that is on the same row as our active cell by the cell above in row 1.

Here's how we do it:

click on B2 to select it and hit F2 to edit the formula (or double click your mouse, click in the editing bar - whichever you prefer).

Now hit the F4 button - your A2 cell reference should change to $A$2. hit F4 again and it should change to A$2 - a third press of F4 and you should have $A2. This is what you want. This makes the column reference absolute - that means that wherever you copy this formula to, it will always refer to column A.

Copy that formula across and see what happens - you should get the numbers 1 to 12 - correct for the one times table.

Next, you need to do the equivalent thing for the row reference - click on cell B2 again and select the reference to B1. Hit F4 twice to make it =B$1*$A2. Now you can copy this to any other cell within the grid and it will give you the correct multiple from the times table - try just one for now - did it work?

OK now for a really useful keyboard trick - first select B2 to M13 so that the whole grid area is highlighted, with B2 as the active cell. Hit F2 to edit the formula, now hold down the CTRL key and hit enter.

This populates all those highlighted cells with the same formula, keeping the absolute and relative references as they are in the active cell. Each cell now contains a formula with one cell reference that is relative row, absolute column times another that is absolute row, relative column.

There - one formula to do the whole times table. See an example of the finished file timestable.xls