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 Office 365.

**Professional Development Guidance!** Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out *Professional Excel Development* today!

With a long list of items in a worksheet, you may want to determine the last time a particular item appeared in the list. ...

Discover MoreIn a series of values you may need to know the smallest value that isn't a zero. There is no built-in function to do ...

Discover MoreIt is not unusual to use worksheets to collect information over time. As you keep adding information to the worksheet, ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2020-01-04 20:25:16

Peter J Moran

2020-01-02 10:39:59

David Robinson

Instead of =$A1+$B1-$C1*$D1 the formula would be =RC1+RC2-RC3*RC4.

Worth considering perhaps.

Happy New Year Allen and everybody, keep warm guys (except in Australia, wishing rain for you!)

2019-12-30 12:49:54

Roy

Womder when they put the Ctrl-a thing in? That's something requested by folks on their UserVoice site, and I voted, but don't recall any announcement that it existed.

Acts in a way I didn't anticipate though. I thought when they did put it in, if ever, it would just be useful for basically copying, pasting over, or deleting. Not a lot else. But letting a command like F4 act on all selected items surprises me. I never thought of it doing that in the past either: seelct the things (plural) I'd like to do that to, then do it to all at once. Since this is allowed, I suspect I missed out for decades where I might've wanted to change, say, 6 of 35 references and could have rather than select them one at a time and do them one at a time. Ah well. I guess I lived.

Good to see Ctrl-a working in edit mode though.

2019-12-29 10:30:15

J. Woolley

Well, Ctrl+A selects the entire formula in edit mode or in the formula bar using my Excel for Office 365 (32-bit, version 1911) on Windows 10 (64-bit).

2019-12-28 16:30:47

John Mann

2019-12-28 10:17:13

J. Woolley

=A1+B1-C1*D1

It does not require 12 keypresses to use the F4 key to change each cell reference to the $A1 variation. You only need to press Ctrl+A to select all, then press F4 three times—a total of 4 keypresses, with no need for positioning.

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2020 Sharon Parq Associates, Inc.

## Comments