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:
=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.
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!
If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...
Discover MorePostal codes in Canada consist of six characters, separated into two groups. This tip explains the format and then shows ...
Discover MoreIf you have two columns containing dates and weights from those dates, you may want to pick a date associated with a ...
Discover MoreFREE 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
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.
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 © 2024 Sharon Parq Associates, Inc.
Comments