Improve the Conditional Formatting Manager
So that we can easily manage conditional formatting. While we're at it, the way conditional formatting is treated when cutting / copying and pasting etc could be reconsidered.
[Following examples added by Dan [MS] for clarity on 10/20/2015]
* name rules
* add a comment to rules
* more easily see what rules apply to what range
* see which rules can be consolidated b/c they are similar
* etc.
Levi Bailey
shared this idea
Thanks for the suggestion Levi! We’ll be taking a look at this along with some other asks around conditional formatting. It’s a big help to see the things with the most votes, particularly within areas like formatting. So please keep the votes coming for things you want us to do sooner!
Thanks,
John [MS XL]
47 comments
-
Ron Kaminker
commented
Conditional formatting is so cumbersome to use.
In the rules manager,
a. you can't see all of the ranges to which it applies
b. it is really difficult to edit
c. when you move cells, you have no idea which cells are being included/excluded from the conditional formattinge.g. I have the below, for my conditional formatting
=$E$124:$N$129,$E$298:$N$298,$D$290:$N$297,$E$36:$N$37,$E$107:$N$122,$E$277:$N$289,$E$296:$N$296,$E$195:$N$198,$E$132:$N$182,$F$199:$N$199
-
Christopher Hazel
commented
The reason I mentioned the comment is that it's probably low hanging fruit. I've had reason to play around with CF in VBA recently and I don't remember the specifics of the object model for CF but the fact that they can be listed in a dialogue box gives you the first clue that they can be iterated as a collection of objects. I figure adding a comment property to an object that already exists in the object model shouldn't be a big deal, and adding the contents of that property to the iteration in the dialogue box should be similarly pretty easy.
-
Roy
commented
Oh, and an Add-In could create those NR's one specifies if they do not already exist. Further ease our pain.
-
Roy
commented
I don't use VBA much, and when I do I'm more like a "hunt and peck" typist than a "touch" typist, so to speak. So I don't know the tools it has for addressing CF. I DO know that CF is not attached to cells in the way normal formatting is, i.e.: the tools for formatting and working with formatting don't even reveal its presence, apparently.
However, the places that say that last part seem to indicate it CAN be addressed, so perhaps there is a reasonable minimum of tools. If so, it seems an instruction to define a range to apply the CF to would be near the top of the "these be basic" list.
Assuming so, one could create NR's for all the "applies to" boxes that could change, or simply all that are in one's CF (I know, that'd be a bear for some folks' CF-ing, but even still, this would greatly simplify their making sure the ranges stay right), and write a macro that changes the "applies to" for all rules by applying the appropriate NR to each.
If a standalone macro, one could run it any time, especially right after a change one made that one feared would change things. It could be called from an OnOpen macro to make sure they are set right upon opening the file.
So whatever happens, running it would simply reset them all to their NR's: I know Excel will instantly convert them, but who would care at that point? They'd be right, either way. Oh, "drag and drop" orphans would be lost, but again, who among us cares? We are not the people doing that.
Someone with the skills and time could write an add-in that might list the rules interactively, let you define the NR's to use and where, and provide the macro to update them whenever along with an option for OnOpen.
And someone at Excel could do that too, offered along with distributions the way the Analysis ToolPak is (Why would anyone ever not want that loaded? Why is it an option to load rather than to unload when first installing?).
-
Nick
commented
Conditional formatting is powerful but a mess. It is easily the thing that makes a 1 hour edit for me a whole day hair pulling affair. So many good comments and suggestions here. Giving back to the community, I found saving a bespoke word Doc (per range, tab, workbook depending) works well. Have a table in there with all the c. f. cells and formulae and formatting. Find+replace helps with duplicating similar rules or ranges. Yes this is an additional step, but it has saved my sanity. Otherwise, great product, MS! (edit: does anyone else's rule formula cell ranges change to some random cell miles away from where I'm working? Drives me nuts).
-
Christopher Hazel
commented
Adding a comment seems like it'd be the easiest thing to implement. I hope you'd do that even if other improvements aren't ready.
I've been taking to encapsulating my custom rules around an AND() argument so I can include an argument that doubles as documentation:
"text describing why I have this rule" = "text describing why I have this rule"
-
Phil Edwards
commented
Allowing window to be resized would be very useful.
As would allowing to select multiple rules with shift key to delete many rules at once.
-
Anonymous
commented
2019 and nothing changed :)
-
Xavier
commented
Could we edit the rules without the conditional formatting manager - directly onxml file or text file
-
Anonymous
commented
Also, the textboxes where we put the formulas and the cells affected by them are very small.
They, or the manager form, should be re-sizable. -
tirlibibi17
commented
Agree with Jon Wittwer. A "duplicate rule" button would be very nice. But what I hate the most is the mess that the ruleset becomes when the worksheet starts living, with duplicated rules on non-contiguous ranges all over the place.
-
alex
commented
Cutting and Pasting totally destroys conditional formatting....it needs work.
-
Fred J. Cimo Jr. commented
These changes are needed as patches for earlier versions of Excel (like - 2010 etc.).
-
FaceTheMusic
commented
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.
-
Greg Chamblin
commented
Why can't I vote for this more than once?!?!?!? J/K
Seriously, this is one of the most neglected areas of Excel.
-
Anonymous
commented
I'd love to have the ability to enlarge or full screen the rules editor and multiple select / delete rules... it is such a long process when copy-pasted rules has lead to multiple instances.
(Worse even when accessing a file through a home network, selecting&deleting them one by one entails a lag in response where the application seems to crash for a few seconds until it becomes responsive again.) -
Brodie
commented
At the very least:
1) make the Conditional Formatting Rules Manage dialogue box bigger,
2) allow multi section for moving rule precedence and deletion of rules (having to move each rule individually is ridiculously inefficient).
3) if you apply a rule to a column or row range, don't break the range into many new rules when cutting and pasting and inserting new columns and rows etc.These three very simple things will make using the Condition Formatting Rule Manger so much easier to use and would take very little effort or required discussion to implement.
Thanks
-
Gerdami Des Betes commented
While I welcomed predefined conditional formatting rules with Excel 2010, I found that the original version was easier to manage.
On the web you can find articles about "conditional formatting nightmare", such as
http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/
Just another example: if you want to grey #N/As, intuitively you may try "cell value = #N/A": it does not work. You have to use a formula such as "=ISNA(cell)".
Please make it simple to use!
-
Omar Khan
commented
Another comment about "breeding" or "fragmenting" rules - if the same rule is applied to contiguous ranges, it would be nice if they could be auto-merged. So if I have a rule applied to A1:B10 and due to copy/ paste it is applied to C1:D10, it could auto-merge the ranges to have a single rule applied to A1:D10. Alternatively/ Additionally, there could be a check box on the CF window to "auto extend during copy/ paste" to enable/ disable the breeding; ideally this would be controlled at the rule level and not globally at the worksheet or workbook level.
And like Excel Help said on Aug 31, 2017 - can we get an update? Or at least more detail into what "under review" means?
-
Anonymous
commented
+vote: Prevent row and column inserts from fragmenting a CF range.
+vote: allow dialog box expansion to more easily see complex (ie existing fragmented) ranges.
+vote: allow ready toggling of range reference behaviour for cursor movement, defaulting to edit formula instead of cell reference insertion
New? vote: Sequence complex (fragmented) cell references by start column of reference so that references can be worked on in workbook-friendly left to right manner.
Matt Townsend