EXCEL

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.