FORMULAS AND WORKSHEETS

[askaboutexcel]

What is the difference between absolute and relative cell references?

 

[tactps]

An absolute reference does not move when you copy and paste it. A relative one does.

The "absolute" is shown by a "$". For example:

$a$1 - this is absolute. when you copy it it will not change
$a1 - this has an absolute column. When you copy it down, the row number will change. When you copy accross, the column will stay as "A"
a$1 - this has an absolute row. When you copy it down, the row number will not change. When you copy accross, the column will go to B, C, etc
a1 - relative. Both the column and row will change.

Type the formula:
=$a$1 into cell B1. Then copy and paste it down and accross. Then do it with the other formulas and see how they change.

 

[tactps]

In VBA the same issues apply, for example:
range("A1")
is an absolute reference. It is always cell A1.

activecell.
is a relative reference. It depends which cell has been activated.