Excel - Absolute Cell References

Absolute Cell References When you copy a formula the cell references automatically adjust according to the row and column that you are in. This is known as Relative cell references. Most of the time this is exactly what we want but sometimes when we copy a formula we do not want all of it to change. For example you may be adding VAT to some figures. You have typed the VAT rate in just one cell and when you copy that formula you want the reference to that cell to be fixed. This is known as an Absolute cell reference.

For example in the above spreadsheet the formula we would put into E6 (alongside the first invoice) would be =D6*B3. The answer would be correct in the first cell but if we copy it down we will receive the wrong answer as per the example below:

This is because the formula in E7 will be =D7*B4. There is of course no value in B4 and B5 has text in it. What we need to do is fix the part of the formula that refers to the VAT reference. To do this you enter the formula as normal but before you press enter after clicking on the VAT cell – B3 – you press the F4 key. This will put a dollar sign before the row and column letters thereby fixing the formula. When you copy the formula each cell will remain pointing to cell B3. The new formula in cell E6 will be:

=D6*$B$3 Use the absolute cell reference whenever you wish to refer to a value which appears in just one cell – e.g. exchange rates, percentages, VAT etc.

Provided by Abacus Computer Training t: 01923 710552

PDF Creator - PDF4Free v2.0

m: 07885 232030 e: [email protected]

http://www.pdf4free.com