In Excel, you can use Relative References (references that change as they are copied to other cells) or Absolute References (references that remain constant if they are copied to other cells).

These are discussed individually below.

Relative References

By default, Excel cell references are relative references. This means that a simple cell reference, used within an Excel cell, will be adjusted when copied to other cells.

For example, in the spreadsheet on the left-hand side below, cell A1 contains a reference to cell E1. The spreadsheet on the right shows the result of cell A1 being copied to cells A1-C3. It is seen that:

• when copied into the columns B and C, the reference to cell E1 adjusts to reference cells in columns F and G
• when copied from row 1, into the rows 2 and 3, the reference to cell E1 adjusts to reference cells in rows 2 and 3.

Absolute References

There are occasions when we need Excel cell references to remain constant when copied to other cells. In this case, we can use the $ symbol before a column or row reference, to make a cell reference absolute. This is shown in the example below, which uses the absolute cell reference, $E$1.

As shown in the spreadsheet on the right, when the reference =$E$1 is copied to cells A1-C3, the reference remains constant:

Absolute and relative referencing can be mixed. For example in the example below, the reference =$A1 uses absolute referencing for the column and relative referencing for the row. The result of this is that the row reference adjusts as cell A1 is copied into rows 2 and 3. However, the column reference remains constant (referring to column E) when cell A1 is copied to other columns. The results of this are shown in the spreadsheet on the right below.

A further example of mixed absolute and relative Excel cell references is shown in the example below. In this case, the reference =E$1 in cell A1, uses relative referencing for the column and absolute referencing for the row. The results obtained by copying cell A1 into adjacent cells are shown in the spreadsheet on the right below:

References to Ranges

The relative and absolute referencing rules apply to ranges, as well as to individual Excel cell references.

For example,

The reference $A1:$E1 becomes:

• $A1:$E1 when copied across to other columns
• $A2:$E2 when copied down by one row

The reference A$1:A$5 becomes:

• B$1:B$5 when copied across to the right by one column
• A$1:A$5 when copied down by one row