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


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:


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


Flipping a Drawing Object

Don't like the way a drawing object looks? Perhaps flipping the object could help appearances. Excel allows you to flip ...

Discover More

Defining Default Printers on a Document Level

If you use multiple printers, you may wonder how to set each document in Word to remember which printer to use for that ...

Discover More

Locking the Size of Pictures in Comments

Excel allows you to place pictures inside of comments. If you do this, you may have experienced a situation where the ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

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

Adding a Missing Closing Bracket

When working with large amounts of data, it is a good idea to make sure that the data all consistently follows a pattern. ...

Discover More

Combining Numbers and Text in a Cell

There are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done ...

Discover More

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

View most recent newsletter.


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 one more than 7?

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


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:
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

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.