Use the function key F4 to toggle a reference between relative and absolute references while editing a formula. Repeatedly pressing F4 will cycle through the 4 different options in this order: A1, $A$1, A$1, $A1.
If you want to toggle all the references in the formula at the same time, just select the entire formula. The first time you press F4, all of the references currently selected will change to the same reference format. For example, if the first reference in the selection is $A$1, then all of the references will change to A$1.
To change just a single reference within a formula I've found that the fastest way for me is to use the mouse to click on the reference within the Formula bar and then press F4 (rather than double-clicking on the cell, then clicking on the right reference).
For me, the time when it is NOT faster to use F4 is when I want to toggle a reference to (or from) A$1 or $A1. I can never remember which one comes first and I inevitably press F4 too many times and end up in an eternal loop trying to get to the right format. Going from $A$1 to A1 is just a quick 3 presses of F4, which I can remember.
By the way, when you are not editing a formula, F4 is also the alternative shortcut to Ctrl+y ('repeat' or 'redo').
No comments :
Post a Comment