Microsoft Excel has two types of cell reference – Absolute and Relative, and an understanding of how these references differ and work can help the spreadsheet designer to save vast amounts time when compiling their spreadsheet, especially when they are copying formulae or worksheets around a workbook.
To best describe the difference between the two types of referencing it is probably easiest to use a real life example.
Consider a worksheet with data in the following cells:
- Cell A1 contains the rate of conversion for Pounds Sterling to U.S. Dollars.
- Cells A3-A7 contain values in Pounds with the values – £1, £5, £10, £25, £100
- Cells B3-B7 are to contain the equivalent values in dollars.
Anyone who is familiar with Microsoft Excel will know that entering the formula =A3*A1 into cell B3 will give the correct conversion, however, should you then copy cell B3 down to cell B4-B7, you would notice that the formula changes automatically as it’s copied, so for cell B4 the formula will read =A4*A2. This is what is known as Relative Addressing as Microsoft Excel will change the originating cell references in relation to the new cell reference, and normally this type of behaviour is quite useful as it means that as you drag formulae down A3 changes to A4, then to A5 etc. which will reduce the amount of typing that is required to complete a table.
This does of course present us with a small problem in that we now have to change the reference to the conversion rate for every cell that we have copied the formula into, so that it it always points back to cell A1, and this is where Absolute Addressing becomes useful.
To prevent the changing of cell reference A1, we can prefix the reference with a dollar sign ($) so that Excel knows that it must now change it when the cell is copied. So using our above example, if we were to enter the formula =A3*$A$1 into cell B3, then copy it down to cells B4-B7 the formula would be copied to cell B4 as =A4*$A$1 and to B5 as =A5*$A$1 etc.
Relative cell addressing doesn’t have to apply to all parts of the cell reference, you can apply it to both the row and column reference as we have done in our example above, you can apply it to just the column part with the reference $A1, or just the row part with the reference A$1.
So to re-cap, there are essentially four types addressing:
- Relative addressing (B3=A3*A1)
- Full Absolute addressing (B3=A3*$A$1)
- Row only Absolute addressing (B3=A3*A$1)
- Column only Absolute addressing (B3=A3*$A1)
To make quick and easy changes to the cell referencing in a formula, just select the cell so that the formula is displayed in the formula window, and then repeatedly press F4 to cycle through the four options above.