Excel Cell References

Relative and Absolute References

If you are using Excel cell references within your formulas, it is important to understand how these can change when they are copied or moved into different cells of a worksheet.

There are actually two different types of Excel cell reference, which behave differently when moved into different spreadsheet cells.

These are Relative References (which change when they are moved to different locations) and Absolute References (which remain constant when moved to different locations).

These two reference types are discussed in detail 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:

when copied into the columns B and C, the reference to cell E1 adjusts to F1 and G1;
when copied from row 1, into the rows 2 and 3, the reference to cell E1 adjusts to E2 and E3.

Original Reference:

	A	B	C	D
1	=E1
2
3

Reference Copied To Other Cells:

	A	B	C
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:

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

Reference Copied To Other Cells:

	A	B	C
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.

Original Reference:

	A	B	C	D
1	=$E1
2
3

Reference Copied To Other Cells:

	A	B	C
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.

As shown in the results spreadsheet, the relative column reference adjusts as it is copied to other columns, but the absolute row reference remains constant.

Original Reference:

	A	B	C	D
1	=E$1
2
3

Reference Copied To Other Cells:

	A	B	C
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

Return to the Basic Excel Page
Return to the ExcelFunctions.net Home Page