» Changing an Absolute Reference to a Relative Reference or Vice Versa in Excel 2007

Relative Reference

When a formula is copied, a Relative reference is used. A Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula.
For example: In cell A1, enter the number 100, and in cell B1, enter the formula =A1. Cell B1 is in a one column distance from cell A1. When the formula is copied from cell B1 to cell B10, the distance between the reference and the cell containing the formula remains one column, and the formula in cell B10 is =A10.

Absolute Reference

Select cell B1 (as in previous example). In the Formula Bar, select A1, (or just click on A1). Then press F4. The result is =$A$1.
Copy the contents of cell B1 to cell B10. Notice that the formula does not change, the formula reference remains constant as =$A$1.

The F4 Key

The F4 keyboard shortcut has four combinations:
State 1: Absolute reference to both column and row, =$A$1
State 2: Relative reference (column) and Absolute reference (row), =A$1
State 3: Absolute reference (column) and Relative reference (row), =$A1
State 4: Relative reference to both column and row, =A1