To quickly change the reference type in a function argument from relative to absolute and vice versa, do the following:
1.Switch to cell editing mode in one of the following ways:
•Select a cell by double-clicking.
•Press F2 (Windows, Linux) or Fn+F2 (macOS).
2.Place the cursor on the desired argument or select it. To convert all arguments in the formula, select the entire formula.
3.Press F4 the required number of times. Each click will sequentially convert the reference as specified in the table:
Click number |
A1 Style |
R1C1 style |
|---|---|---|
Before clicking |
=A1 (relative reference) |
=R[1]C[-1] (relative reference) |
1st click |
=$A$1 (absolute reference, row and column are locked) |
=R2C1 (absolute reference, row and column are locked) |
2nd click |
=A$1 (mixed reference, row is locked) |
=R2C[-1] (mixed reference, row is locked) |
3rd click |
=$A1 (mixed reference, column is locked) |
=R[1]C1 (mixed reference, column is locked) |
4th click |
=A1 (relative reference, return to initial state, start of new cycle) |
=R[1]C[-1] (relative reference, return to initial state, start of new cycle) |