Forum Discussion
Cannot edit conditional formatting formula.
I am unable to edit a conditional formula. If I have a formula
=OR(B1>C1,C1>D1,D1>E1)
and I want to change the formula to be
=OR(B1>=C1,C1>=D1,D1>=E1)
I cannot put the cursor after the ">" nor I can use the left/right arrow keys to move the cursor there. Using the left/right arrow keys results in entry to cell references into the formula. I have to erase the entire formula and start over. Drives me insane. How do I put the cursor in the middle of the formula or move it to where I need it?
(The huge cell reference numbers are created by Excel, another pain the ...). Yes, I can see that the cursor movement keys enter the reference to a cell, but I want to EDIT the formula, not have Exel so helpful I am nauseated. Any help with this would be much appreciated.
17 Replies
Using the F2 key did not work for me, but I was able to get past the bug by holding either the shift or control and hitting the back arrow.
If you are using or can open the file in Excel online, I find it a lot easier to edit the conditional formatting formulas through there, it allows you to place the cursor exactly where you want.
I can't believe I'm saying this (actually, I can believe it) - this very annoying bug is STILL there, in the current version of Excel 365, almost a year later. I've been struggling to set up some conditional formatting all morning, and, without knowing about the F2 trick mentioned here (which thankfully works to allow editing ranges with the arrow keys - the mouse still doesn't work), it's much harder than it should be because of this bug. I'm not sure if Microsoft actually takes notice of these forums at all - all the complaints here about things that don't work properly appear to be just a way for irritated users to vent.
Anyhow, add this one to the other bugs and annoyances that come up in searches from desperate users only to find someone upset about the exact same thing years (or even decades) earlier.My SINCERE thanks to everyone who took the time to read my missive and effort to answer it. I did not know how to get back here, so my apologies for the late reply.
I do not know how to find right now the names of most effective replies. I apologize.
Thank you all!
Select the range to which you want to apply the rule.
The first (top left) cell in that range should be the active cell in the selection.
Note the address of that cell in the name box on the left hand side of the formula bar.
On the Home tab of the ribbon, select Conditional Formatting > Manage Rules...
Select the rule, then click Edit Rule...
Click in the formula and press F2. You should then be able to use the arrow keys, as well as the Home and End keys, to move the insertion point.
Make sure that the formula is correct with respect to the active cell. For example, if the range starts in row 1, the formula should refer to B1, C1 etc.
But if the range starts in row 4, the formula should refer to B4, C4 etc.
- Afraid you are not understanding the OP. This is a real software bug. I have the same thing happening with conditional formatting using formulas. On a sheet of even moderate complexity, with conditional formatting formulas applied to whole column ranges, the formula edit text field will at some point not accept mouse clicks any more. This means you cannot position the cursor into formulas to make adjustments.
If you close and reopen excel, you can get ONE mouse click to edit ONE formula. Next time you open conditional formatting, you again can't click in the field and must save and restart excel again to be able to edit exactly one more.
This is especially difficult since despite ANY logic saying it's a good thing to do, the left and right arrows are hijacked at all times regardless of UI focus, so you cannot cursor in to edit parts of the formula with a keyboard. (A bad design choice i won't get into here.)
Bottom line: This is not the users fault, it's a real bug that bot past Microsoft QA.
For anyone suffering this bug: CTRL-S to save, then close excel, then reopen the workseet. You'll probably be able to click the mouse one time to make a conditional formatting formula edit before the bug arrives. Rinse and repeat until your sanity wears out.You seem to have misunderstood HansVogelaar.
He didn't say you would be able to edit the formula with a mouse, he said that you can override the hijacking of the left and right arrows by pressing F2 whilst in the edit box. This allows you to move the cursor with the left and right arrows.
