Changing the Cycling Sequence for the F4 Cell Reference Shortcut

Written by Allen Wyatt (last updated April 22, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


6

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Saving Embedded Images to Individual Files

Word has long allowed you to embed pictures or images in the documents you create. What if you want to get those pictures ...

Discover More

Excel Refuses to Put Page Breaks between Subtotal Groups

Page breaks not appearing where you expect them in your subtotaled data? It could be because of a setting you made in ...

Discover More

Changing Text Case Many Times

You can use the built-in Word shortcut to change the case of a text selection. You may have quite a few items in a ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Counting Only Money Winners

If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...

Discover More

Formatting Canadian Postal Codes

Postal codes in Canada consist of six characters, separated into two groups. This tip explains the format and then shows ...

Discover More

Returning a Weight and a Date

If you have two columns containing dates and weights from those dates, you may want to pick a date associated with a ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six minus 4?

2020-01-04 20:25:16

Peter J Moran

If Ctrl+A will not work, as I found on Excel 16, Shft+Ctrl+End certainly achieves the same result in Edit mode.


2020-01-02 10:39:59

David Robinson

If Heather generally needs to reference the leftmost cell in her current row, she might find it easier to use R1C1 notation. It needs three keystrokes to insert the reference RC1, which is no fewer than $A1, but I find it easier because I don't need to factor in which row I'm in when typing the formula. I'm in the UK, where I need to hit two keys to get $ (shift + 4: is this the same in the US?), so actually RC1 is easier for me to type.

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

Works for me too: Version 1911 (Build 12228.20364 Click-to-Run)

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

@John Mann
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

I couldn't get Ctrl+A to select the entire formula, either in the cell continaining the formula (in edit mode) or in the formula bar.. Excel 2010 in Windows 10


2019-12-28 10:17:13

J. Woolley

Consider the formula referenced above:
=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.


This Site

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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.