Cannot edit conditional formatting formula.

[NummerSechs]

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.

[#2]

@NummerSechs 

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.

[#3]

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.

[#4]

@Zeb5585 

If you want to use the left and right arrow keys to move through the formula, press F2.

[#5]

Helpful tip! I wish I knew that F2 trick in conditional formatting 20 years ago....

Bug still remains though - I have sheets where the mouse will not click into the conditional format formula edit box. Some days it happens less, some days more. But F2 at least is a work around to get in there with my keyboard and tweak a formula rule without restarting the entire app. 👍



Thanks!

[#6]

@Zeb5585 When you click 'Edit Rule...', can you use the Tab key to set focus to the formula box?

[#7]

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!

[#8]

Next time it happens I'll try that, good thinking. The keyboard hijacking i described earlier trains most people to try to fear touching their keyboard when in these formula edit dialogs. Seriously if your mouse / UI focus isn't on a spreadsheet, why hijack arrow keys? If one clicks a cell while that dialog is open set a range, well then hijack away. Sigh. Feels like the people making the software have never used the software.

Anywho, I've got no recreate steps for the bug, it's quite random. Re: the evil workbook it was happening in, i couldn't recreate this morning when I tried, though i wasn't really exercising the software much since the sheet is in a finished state now and I don't want to mess things up. I've also got an employer who I'm sure doesn't pay me to provide free QA services to Microsoft. ;-/

[#9]

Confirming this is a bug - happening to me too with Excel 365. Mouse will not put cursor in certain positions within the formula box. The Tab key does allow to focus on the formula box but doesn't solve the issue.

[#10]

Good day. Yip it is a definite bug. I experience that as you edit it becomes more of a problem as if the cursor alignment deteriorates. When I save go ou and them come back in again I can click in the formula to move the cursor, as I edit more formulas in the same session, I have to click high in the formula line to move the cursor to that insert point, then it becomes so misaligned that clicking does not work at all. I can still highlight and select the complete formula by clicking at the back and then move cursor up and left???

I also just want to say thanks for the F2 tip, it is mighty handy! Take a hi3 for that. 🖖

[#11]

PS : It seems the same is happening when editing data validation messages.
Also after a cold start of my PC I can edit quite OK for a while.
Maybe these input will assist Microsoft in getting the the root cause.
It surely seems as if the cursor loses its focus over time. Might even be Windows and not Excel specific.

[#12]

@HansVogelaar thank you soooo much never though of that and was running crazy with this bug! 

same bug happen at time to select a cell by the way where the mouse kind of offset the click by 2 or 3 cell, and it is always to the right.

[#13]

Thank you for the explanation. This BUG is really annoying

[#14]

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.

[#15]

Wow! Thank you so much for mentioning that F2 shortcut! This issue has long been a productivity hampering bug in my work flow for years. Good to know there's a work-around instead of having to completely shut down Excel and lose track of all my open spreadsheets.