Changing the Cycling Sequence for the F4 Cell Reference Shortcut

When creating a formula, Heather notes that she can press F4 to cycle through the various permutations of a cell reference. Most often she presses F4 three times to get to the $A1 reference that she needs. Heather wonders if the cycling order can be changed so she doesn't need to press F4 as many times.

There is no native way within Excel to modify the cycling order for the F4 key. To make the problem even stickier, there is no way to change it using a macro. (This is because you cannot run macros while you are in "edit" mode for a formula.)

Given those two realities, it may be helpful to think through how the F4 shortcut is being used. The purpose of the key, while editing a formula, is to cycle through the reference modes of whatever cell reference the insertion point is located within. Thus, if the insertion point is within a cell reference such as B7, pressing F4 multiple times will cycle through the following permutations, in this order: $B$7, B$7, $B7, B1. Keep pressing F4 over and over, and you'll keep cycling through these four variations.

If you are entering your formula from scratch and you wanted to get to the $B7 variation, then it would seem that pressing F4 is not really a shortcut—it is simpler just to type the dollar sign where you need it. Doing so requires just a single keystroke, where pressing F4 requires 3. Even if you were able to reverse the cycling order of the F4 key, it would still require a single keystroke, the same as simply pressing $.

This fact is compounded if you are entering or editing a more complex formula. Consider a formula such as the following:

=A1+B1-C1*D1

To use the F4 key to change each cell reference to the $A1 variation, you would need to position the insertion point four times and, after each positioning, press F4 three times—a total of 12 keypresses, not counting the positioning. In such a case it is much easier to simply position and press $ at the beginning of each cell reference.

You could, if desired, create a macro to modify the formula in a cell outside of edit mode. This may seem a simple exercise, but it is probably more difficult than you may realize. Consider the following formulas:

=A7 & " days and " & INT(A8 * 24) & " hours."
="(" & A7 & "+" & B7 & "^3)/" & C7 & ": " & (A7+B7^3)/C7
="(A7+B7^3)/C7: " & (A7+B7^3)/C7

The second and third examples are simply variations of each other, but they illustrate the point that finding cell references in the middle of the formula can take some doing. Your code would need to analyze the formula and ignore everything within quote marks and then figure out if something is actually a cell reference or not. It would also need to ignore things like named references that may be in the formula. It would also need to strip out any $ characters that may already be used in a cell reference before converting the cell reference to the desired $A7 format.

Accounting for all these possibilities—particularly when the formula may well contain many, many cell references—is not a trivial task!

Which brings us back to the idea that it may be easier to simply bypass the F4 key all together and simply type the dollar sign in the desired location.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13718) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.