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 when they are copied to other cells) or Absolute References (which remain constant when they are copied to other cells).

These two reference types 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 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:

 Original Reference:
 ABCD
1=E1   
2    
3    
 Reference Copied To Other Cells:
 ABCD
1=E1=F1=G1 
2=E2=F2=G2 
3=E3=F3=G3 

Absolute References

There are occasions when you need Excel cell references to remain constant when copied to other cells. In this case, we can use the $ symbol before a column and/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:

 Original Reference:
 ABCD
1=$E$1   
2    
3    
 Reference Copied To Other Cells:
 ABCD
1=$E$1=$E$1=$E$1 
2=$E$1=$E$1=$E$1 
3=$E$1=$E$1=$E$1 

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.

 Original Reference:
 ABCD
1=$E1   
2    
3    
 Reference Copied To Other Cells:
 ABCD
1=$E1=$E1=$E1 
2=$E2=$E2=$E2 
3=$E3=$E3=$E3 

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:

 Original Reference:
 ABCD
1=E$1   
2    
3    
 Reference Copied To Other Cells:
 ABCD
1=E$1=F$1=G$1 
2=E$1=F$1=G$1 
3=E$1=F$1=G$1 

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 (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