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...
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.
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.
- Thank you for the explanation. This BUG is really annoying
- 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. - 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. 🖖 If you want to use the left and right arrow keys to move through the formula, press F2.
- 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.
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.
- 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!Zeb5585 When you click 'Edit Rule...', can you use the Tab key to set focus to the formula box?
