Relative and Absolute References

The usual cell references (like A2 or C6) that we enter in a cell formula are actually what Excel calls a relative reference. For example, if we enter the formula =2*A1 into cell B1 as shown below, Excel will of course double the value of A1 and insert the result in B1, as we already know. But if we select cell B1, choose "Copy" from the Edit menu, and then paste in cell B2, we don't get the formula =2*A1 anymore; instead it is =2*A2.

 
A
B
Formula:
1
-1
-2
=2*A1
2
0
0
=2*A2
3
1
2
=2*A3
4
2
4
=2*A4
5
3
6
=2*A5

In fact, if we fill or copy the formula down, we see that each formula must really be "2 times the cell to the immediate left of this one." This is why these are called relative references.

You can however make sure a reference is fixed by making either the row number or column letter (or both!) an absolute reference. To make something an absolute reference, just put a dollar sign ($) in front of the row number or column letter. Two examples are shown below, using the same basic formula as above. In the first example, we have made the row absolute, and in the second the column. We have then copied the formula to the first two columns to the right:

 
A
B
C
1
-1
-2
-4
2
0
-2
-4
3
1
-2
-4
4
2
-2
-4
5
3
-2
-4
 
A
B
C
1
-1
-2
-2
2
0
0
0
3
1
2
2
4
2
4
4
5
3
6
6
Fixed Row: Formula =2*A$2 entered in B2, then copied to cells B2:B5 and C2:C5. (The entry is always taken from Row two, but the column is always to the immediate left.) Fixed Column: Formula =2*$A2 entered in B2, then copied to cells B2:B5 and C2:C5. (The entry is always taken from Column A, but the row is always the current row.)

Return to Excel Guide