Excel Cell References
Relative and Absolute References
Many Excel formulas refer to cells or ranges of cells. For example, the simple formula
=A1+B1 refers to the cells A1 and B1.
However, many users don't realise that there are different types of references, which behave differently when formulas are copied into different locations in an Excel Workbook.
The two types of reference are Relative References (which change as they are copied to other cells) or Absolute References (which remain constant as they are copied to other cells).
These two reference types are discussed individually below.
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 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.
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:
Mixing Relative and Absolute References
Absolute and relative referencing can be mixed. For example in the spreadsheet 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 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.
The reference $A1:$E1 becomes:
- $A1:$E1 (no change) 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 (no change) when copied down by one row