Changing the Cycling Sequence for the F4 Cell Reference Shortcut

by Allen Wyatt
(last updated December 28, 2019)

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

Automating Copying Macros

You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's ...

Discover More

Entering a Degree Sign

One of the more common symbols that people need to use in their writing is the degree symbol, typically used after a ...

Discover More

Understanding Optional Features

Windows 10 includes many features that are not enabled by default. Discover how to turn on or off the optional features ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

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

Searching for a Value Using a Function

Searching for a value using Excel's Find tool is easy; searching for that same value using a formula or a macro is more ...

Discover More

Counting Cells According to Case

Text placed in cells can either be lowercase, uppercase, or a mixture of the two. If you want to count the cells based ...

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}] 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 seven more than 2?

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.