What Are Relative & Absolute References?

When you copy a formula, you have the choice of making it relative or absolute. Relative references adjust to location. Absolute references remain constant regardless of location.

Relative References
In the example above, Row 8 contains Relative References. Excel has automatically adjusted the copied formulas based on their location. For example, by copying the formula =SUM(B3:B7) from cell B8 to cell C8, Excel automatically adjusted it to read =SUM(C3:C7). The formula becomes =SUM(D3:D7) after being copied to cell D8.

The formula has changed relative to its location.

Absolute References
In the example above, Row 9 contains constant or Absolute References. The first part of the formula is not adjusted, despite its new location. When the formula =$B$10-B8 was copied from cell B9 to cell C9, the B10 reference was not changed, even though the B8 reference was. The formula became =$B$10-C8. The constant or absolute reference to cell B10 was made by placing a $ in front of the column and row designation, in this case $B$10. Thus, no matter where the formula is copied, $B$10 stays the same. The formula becomes =$B$10-D8 after being copied to cell D9. The $B$10 reference is constant, or absolute.

Click HERE for instructions on copying a formula using an Relative Reference.

Click HERE for instructions on copying a formula using an Absolute Reference.

Click HERE to return to the Table of Contents.