Home » Excel-Built-In-Functions » Basic-Excel » Excel-Cell-References

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 E1 contains a reference to cell A1. The spreadsheet on the right shows the result of cell E1 being copied to cells F1-G1 and cells E2-G3. It is seen that:

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

Absolute References

The relative referencing feature that is used by default for Excel cell references is useful for functions and formulae, as we frequently require cell references used in a formula to adjust when copied to other cells.

However, 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 change a cell reference into an absolute cell reference. This is shown in the example below, in which the $ sign is added to the reference A1 that is contained in the cell E1, changing this reference to $A$1.

As shown in the spreadsheet on the right, when the reference =$A$1 is copied to cells F1-G1 and E2-G3, the reference remains constant:

Absolute and relative referencing can be mixed within a single cell reference. 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 E1 is copied into rows 2 and 3, but the column reference remains constant (referring to column A) when cell E1 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 =A$1 in cell E1, uses relative referencing for the column and absolute referencing for the row. The results obtained by copying cell E1 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,