Absolute Cell References

Sometimes you will need to work with absolute cell references that refer to cells by their fixed position in the worksheet. 

1. Scroll down the worksheet until you can see the range B20:F24 with data about VAT. 

First, complete the VAT column

1. Click in C21
2. Type  =B21*F21 and press the Enter key The formula in C21 gives the correct answer (｣19.60) but is not suitable for copying. To demonstrate this, 

 

Copy the formula in C21 to C22.

The value shown will be ｣0.00. This is because F21 became F22 when copied and the value in F22 is zero.

When you are going to copy a formula and do not want a cell reference to change, you should use an absolute cell reference. A dollar symbol, $, is used to fix a reference. When placed in front of a column name it fixes the column and in front of a row number it fixes the row.

So, for example, $F$21 means always column F and always row 21. 

Note: If you click anywhere within a cell reference in a formula, and press the F4 button, dollar symbols will be inserted for you automatically. You can have mixed references such as $Z34 (column is fixed, row is relative) and Z$34 (column is relative, row is fixed). Keep pressing F4 if you wish to have mixed references.

 

Now change the formula in C2

1. Double-click in C21.
2. Edit the formula to read  =B21*$F$21
3. Press Enter.
4. Copy the formula in C21 down to cell C24 and check that the  resulting values are correct. 

Finally, complete the Cost with VAT column

1. Click in D21 and enter a formula for the cost including VAT (cost without VAT plus the VAT to be paid on each item
2. Copy that formula down to D24. Your results should be as shown below

When entering formulae you can, if you wish, refer to cells in other

worksheets of the same workbook and to cells in other workbooks