**When you
enter a formula such as =A1 + A2 you are using relative references.
If this formula were entered in cell A3 it could then be copied across to
cells B3, C3, D3, etc to sum those columns.
As you copy and paste a formula, Excel will update the references so that
they apply to the same relative positions for the new location of the formula.
The example below
shows the results of the formula in A3 being pasted to cells B3 and C3.
**

**
**

**The formula is adjusted to sum the
two cells directly above the result cells as the formula is pasted. While this is often useful, it can cause a problem when you
want to copy a formula containing a reference to a fixed cell or if you want the
row reference to stay the same as you move down a column or the column reference
to stay the same as you move across rows. To
prevent Excel from adjusting the references you should use Absolute references
to fix the reference when it is copied. An absolute reference is created by inserting a $ sign in
front of the parts of the reference that you want to fix, you can force a
calculation to always reference a certain fixed cell, e.g. B5, by using an
absolute reference like $B$5 in your formula.**

**An application of absolute and
relative references can be seen in the times table spreadsheet shown below. We calculate the result by
multiplying the column and row headings. As
we move down a column we do not want the row reference of the column heading
cell to change so that we keep that figure in our calculation so we fix this in
column B by using the mixed reference B$1 which fixes the row reference to row 1
but will allow the column letter to increase as the formula is copied across the
row. We also then need to fix the column reference as we move
across a row so that when we are moving along row 2 we are always multiplying by
the figure in Column A. This is
achieved by using the mixed reference $A2, this allows the row number to
increase as we copy down the column but fixes the column reference to A.
If the formula shown in cell B2 is copied over the rest of the grid, a
correct times table grid results.**

**
**

**Absolute
references are useful in applications like invoices where the rate of VAT is
fixed. The
example below
shows how absolute references could be used in an invoice created from a
spreadsheet to reference the rate of VAT from a single cell.
The active cell is cell H35, this contains the value 0.175. The formulas
in the VAT column are of the form =I16*$H$35, this ensures that when the VAT
formula is copied down the column the VAT cell reference is not changed.**

**
**

**Click
here to
return to the Excel index.**