» Changing an Absolute Reference to a Relative Reference or Vice Versa
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, type the number 100, and in cell B1, type the formula =A1. Cell B1 is one column to the right of 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.
Select cell B1 from the previous example. In the Formula Bar, select A1, and 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 states:
State 1: Absolute reference to the 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 the column and row, =A1
|Screenshot // Changing an Absolute Reference to a Relative Reference or Vice Versa|