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, there are different types of references, which behave differently when formulas are copied into different locations in a workbook. The two types of reference are:

Relative References

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

Example

  A B C D
1 =F1                     
2        
3        
      
  A B C D
1 =F1 =G1 =H1       
2 =F2 =G2 =H2  
3 =F3 =G3 =H3  

In the spreadsheet above, cell A1 contains a reference to cell G1. The spreadsheet on the right shows the result of cell A1 being copied to cells A1-C3:

  • When copied into columns B and C, the reference to cell E1 adjusts to reference cells in columns F and G
  • When copied 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 Excel cell references need to remain constant when copied to other cells. In this case, the $ symbol is placed before a column or row reference, to make a cell reference absolute

Example

When the reference =$F$1 in A1 is copied to cells A1-C3, the reference remains constant:

  A B C D
1 =$F$1                     
2        
3        
      
  A B C D
1 =$F$1 =$F$1 =$F$1       
2 =$F$1 =$F$1 =$F$1  
3 =$F$1 =$F$1 =$F$1  

 

Mixing Relative and Absolute References

Absolute and relative referencing can be mixed. For example in the spreadsheet below, the reference =$F1 uses absolute referencing for the column and relative referencing for the row.

The result is the row reference adjusts as cell A1 is copied into rows 2 and 3. However, the column reference remains constant (referring to column F) when cell A1 is copied to other columns. The results are shown in the spreadsheet on the right below.

  A B C D
1 =$F1                     
2        
3        
      
  A B C D
1 =$F1 =$F1 =$F1       
2 =$F2 =$F2 =$F2  
3 =$F3 =$F3 =$F3  

Another example of mixed absolute and relative cell references is shown in the example below. In this case, the reference =F$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:

  A B C D
1 =F$1                     
2        
3        
      
  A B C D
1 =F$1 =G$1 =H$1       
2 =F$1 =G$1 =H$1  
3 =F$1 =G$1 =H$1