Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references
I previously posted this macro example for converting a bunch of formula-containing cells all at once, from relative to absolute references.
Alternatively, you can quickly toggle a single formula’s row and/or column references for relative and absolute with an easy keyboard shortcut. There’s no VBA or programming involved.
Just double-click the cell, or select the cell and hit the
F2 key to edit the cell…
…then hit the
F4 key to toggle for relative all; absolute row; absolute column; or absolute all.
In this example, cell D15 is selected, which is averaging a few ranges.
With cell D15 selected, hit the
F2 key, or double-click the cell to go into Edit mode.
I selected all the range arguments between the parentheses. You don’t need to select all range arguments, just the one(s) you want to toggle.
With the original formula having all relative references, hitting the
F4 key the first time changes all selected row and column references to absolute. Notice that the changes are concurrently reflected in the formula bar.
The second time you hit
F4, the column references are relative and the row references are absolute.
The third time you hit
F4, the column references are absolute and the row references are relative.
The fourth time you hit
F4, you have completed the toggle cycle and started a new one, right back where you started in this example, with all range references being relative as they originally were.