Relative and Absolute Reference

By default, Excel uses relative reference, e.g.

A1 (relative column and relative row)

But you can change a reference to the following format (keyboard shortcut, F4)

$A$1 (absolute column and absolute row)

A$1 (relative column and absolute row)

$A1 (absolute column and relative row)

Their differences are manifested when you copy formula.

• When you copy a formula, Excel automatically adjust the relative reference based on its relative position to the original cell. So in the following example, “C7”, “D7” become “C8”, “D8” when you copy down.

• When you don’t want EXCEL to automatically adjust reference, you can use “$” to fix the row reference, column reference or both. E.g. In the following example, we want to use fixed row and column for the interest rate (“B2”), but use relative reference for the principal (“A4”).