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.
Yeah toggling through the different reference states is way easier than entering the $ manually every time. Although i usually have to toggle trough all of them at least twice, cause im always missing the correct one the first time 😛 Btw, for those who dont know anything about absolute/relative reference, i would recommend looking at a beginners guide like http://www.excel-aid.com/excel-absolute-reference-and-relative-reference.html. That should help everyone to understand Toms tutorial above 🙂