Forum Discussion
NummerSechs
Jun 11, 2023Brass Contributor
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...
Zeb5585
Jul 06, 2023Copper Contributor
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.
HansVogelaar
Jul 06, 2023MVP
If you want to use the left and right arrow keys to move through the formula, press F2.
- raaomokaJun 06, 2024Brass ContributorWow! 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.
- LoicLApr 04, 2024Copper Contributor
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.
- Zeb5585Jul 06, 2023Copper ContributorHelpful 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!- HansVogelaarJul 06, 2023MVP
Zeb5585 When you click 'Edit Rule...', can you use the Tab key to set focus to the formula box?
- DP_24Sep 22, 2023Copper ContributorConfirming 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.