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]
55 comments
-
Eckhard
commented
Errors (--) and deficits (-)
-- Conditional format causes crashes when applied within a structured table.
- Applies to" allows the specification of name ranges, but converts the named range to coordinates.
The loss of the Named Area is very annoying because you can no longer model efficiently. - In contrast, the reference to the named area remains the same during data validation.
- Styles (Custom styles) are very helpful for the systematic formatting of Excel. However, they are missing in the "Conditional Format" function. There, the benefit would also be very great. -
Stephan Keller
commented
At least make the dialog window resizeable asap, yes, I mean ASAP!
It can't be that hard to implement that.
When I see all the other changes MicroSoft implements to the UI of Office365 (of which, not all of them makes Office365 more user friendly), it just seems to me, that they set wrong priorities on their backlog of tasks.
How many votes must a suggestion receive, until it will be implemented? -
Kevin Osborn
commented
CF is very powerful but seems left behind. Here is my list of needs for CF.
- Make window resizable
- Don't fragment rules when cut/copy/paste/insert/delete rows
- Make formulas work like regular formulas (error checking, auto complete, result preview, volatile only if formula used is volatile)
- Allow referencing of named ranges and structured table reference
- Make search/find feature look inside CF formulas -
Phil
commented
Please also include protection of conditional formatting from copy/paste actions in protected cells
-
Anonymous
commented
why do we bother?
this will clearly never get done, it seems.
even the simple bits that would be so quick (e.g. window re-size, or re-order by format)...
yours frustrated
me -
KBL
commented
Please make the formula text window re-sizable. With long formulas, its hard to see
-
R
commented
TOTALLY AGREE, IT IS STILL PAINFUL EVEN IN 2019. SMH
-
Jay
commented
At a bare minimum, make the window resizable. This is a quick, simple win that shouldn't be held up by any 'stretch goals' for the complex management of conditional formats.
-
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.