Modernize conditional formatting formula editing.
As of 2013, it conditional formatting formula editing is still inconsistent with the regular application, and makes it harder for users to learn conditional formatting, which is one of the more abstract concepts of the early Excel learning process.
Arrow keys are still modifying formulas rather than moving the cursor, for example.
Great suggestion, thanks David! And thanks to other people who took the time to clarify/comment on this one. There’s definitely room to tighten this experience up in a number of places. We’re getting a lot of traffic on the site, so please keep voting for the things you care about most to help us do a great job of prioritizing.
John [MS XL]
Further to that, being able to reset the default mode (ie F2 edit mode) to
suit your personal preference would be great. I'm always forgetting to
press F2, and I've literally never wanted the non-edit-mode behaviour when
editing conditional formatting rules...
Emre Poyraz commented
Hello, I agree with you all. Conditional formatting with formulas is quite a mess.
I think the main problem is this: there is a conflict between APPLIES TO and SELECTION. Excel is trying to choose one row less then our selection and apply the formula accordingly, but messes up.
Compounding this problem: When you have a table in Excel, the first CTRL+A selects the table WITHOUT the header. The second CTRL+A selects the table WITH the header. This makes it more difficult for Excel to decide where to apply the formula to.
I have to manually change this everytime I use conditional formatting with formulas (Manage Rules). Check the first row in Applies to and change to formula accordingly (interestingly, it works well after the first change).
I believe this can and should be fixed.
PS: If you dont want the arrow to point to new cells using arrow keys, press F2.
At a bare minimum, the manager window needs to be resizable. It's staggering that you haven't at least fixed that already.
Olivier Travers commented
Frankly, the whole conditional formatting user experience is a mess. You have to keep cleaning up duplicate rules, the modal window doesn't scale well when you have many rules, the formula editor is hard to work with, we should be able to contain formatting to Tables or Pivot Tables (not just A:A cell selections) etc. This basically needs to be redesigned from the ground up with scalability in mind.
The editor line is much to small
Also, the textboxes where we put the formula and the cells affected by it are very small.
They should be resisable.
Man, the current behaviour has wasted so much of my time! I almost gave up on learning conditional formatting I was making so many mistakes with that.
I say that, and I've spent decades workings a programmer and as someone whose specialty was picking up weird stuff other people couldn't figure out. (I wasn't fast, but I was great at figuring stuff out and very good at not creating new bugs.)
Back in Excel 2007, I can copy and paste multiple cells and conditional formatting rules are merged by default. It will split rules in Excel 2016. What a regression! Please bring back the old behavior. Now it takes extra step to do the same thing (wasted time) and there is a chance you may forget it once in a while. So annoying.
John Loach commented
Also needs to reflect structured table references and defined names instead of auto-converting them to cell references. This is in both the Formula Editor and in the "Applies To...".
As Dave Bonin mentioned below, the formulas "breed" with copy/paste and ctrl+d. I've locked formatting of cells but the formulas still "breed". After a week, a tracking sheet I use will have 1000's of conditions and inputting data gets real slow.
The ability to resize the menu pop-up would be a great starting point.
Omar Khan commented
Another idea related to conditional formatting that can hopefully be considered as well - check box to apply or not apply formula once it is written: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10671168-conditional-formatting-check-box-on-off
Finn Haverkamp commented
Conditional Formatting is the worst interface design in all of excel. The arrow key thing you mention is one great example. Personally, I also dislike when I copy or cut and paste data, it moves the conditional formatting range. I want to select A2:F16 or whatever, and for it to always apply to A2:F16 no matter what I cut or copy or paste.
Lubomír Tosek commented
Not only conditional formating formulas, also NAME formulas - both very similar, BOTH need full ability as formulas in cells.
Also needs comments (may be after end of formula, with comma, space or other character(s))
Dave Bonin commented
In Excel 2010, I often find myself stripping off and then re-applying conditional formats because they tend to "breed" excessively. Normal copy and paste operations fragment formatting ranges and it becomes very difficult to track what the overall format intent is.
On larger workbooks, I've abandoned conditional formats altogether and just used VBA to program what formats should apply and when. Much more work for me up front, but much more stable in the end.
Jan Karel Pieterse commented
Some examples of what makes editing CF formulas awkward:
- The editbox is too tiny, should be sizable and multi-line
- It does not show current results
- You cannot use the function wizard when editing such a formula
it would be useful to link conditional formatting to named ranges
Cathy, another frustrating issue with entering formulas for conditional formats:
If there are a range of cells selected before adding a conditional format, the relative referencing is adjusted to the activated cell, instead of the top left cell of the rectangular selection.
To replicate the steps: select cell B2, extend your selection to A1:B2 (shift+click or shift+arrow), add a conditional format with the condition formula of "=A1", once you are returned to the Conditional Formatting Rules Manager, the formula will have changed to "=XFD1048576". This is because the top/left-most cell in the "applied to" range now refers to one column to the left and one row up, which wraps around to the last row/column.
If instead the top-left cell in the "Applied to" range is used, it will react as expected.
Cathy Harley (EXCEL PM) commented
Ingeborg, thanks for supplying that awesome F2 work around for the arrow keys! Are there other specific examples of things that are difficult when editing conditional formatting formulas?
Ingeborg, Thank you!!
Ingeborg Hawighorst commented
hit F2 while editing the formula to toggle the behaviour of arrow keys (select cell / move cursor)