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]
32 comments
-
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 -
Ewald
commented
I agree to all of those imrovement ideas regarding Conditional formatting
(as I have sheets with >100 CFs).
Additionally - oras a first step - It might make sense to have export and import function for conditional formatting for a worksheet. -
Jon Wittwer
commented
Long formulas are very difficult to edit within the Rules Manager. So, either allow the formula field to be enlarged, or change the behavior of the left and right arrow keys, so that when you press left or right it scrolls the field rather than inserting a +Ref into the formula.
-
Jon Wittwer
commented
A "Duplicate Rule" button would be a VERY useful new feature for the Rules Manager. I've wasted quite a bit of my life on the the task of duplicating rules the hard way (going to Edit Rule to copy the formula, then creating a new rule, pasting the formula, then editing the format, editing the Applies to, and then re-editing the formula after realizing that all references changed because I didn't have the correct cell selected).
-
Ben Smith
commented
Ability to enlarge the edit formula rule box please!
-
Excel Help
commented
All of the above comments, I second. In addition, I've noticed a weird bug where the "Applies to" field turns blank for some rules as I'm scrolling through many rules. Change or delete a rule, and it comes back. But randomly that field is missing the cell references.
Btw MSFT, considering this "under review" for two years isn't very comforting. Can we get an update? Your power users are begging for long overdue changes to CF.
-
Jon Wittwer
commented
I'd like to see a "Consolidate Rules" button included in the Rules Manager that would automatically consolidate all identical rules.
That may be too difficult to implement if it isn't easy to determine whether rules are identical or not. So, I've added the following as a separate suggestion (Make "Paste and Merge Conditional Formatting" the default Paste behavior) for helping prevent the problem of having dozens or hundreds of split/fractured/duplicate rules.
-
an commented
"While we're at it, the way conditional formatting is treated when cutting / copying and pasting etc could be reconsidered."
This is major stuff for me - neatly formatted tables created for easing user input of data get its conditional formatting messed up after a short time. The interface is very clumsy for the complexity of what it does, but for the most part, it's bad that it gets so easily messed up.
One possible action would be to make certain conditional formatting LOCKED from copy/pasting. That alone would likely to prevent a major chunk of the current issues.
-
Peter
commented
Are you able to change the size of the Conditional formatting manager box.
when working with a lot of formatting it would be a lot easier to view if you can enlarge the viewing boxCheers
-
Wyn Hopkins
commented
Good suggestions, especially being able to add a comment to each rule explaining the "why"
-
Jon Wittwer
commented
Like Roland, the biggest issue I have with conditional formatting is that a lot of unnecessary separate rules are created when copying/pasting. For example, inserting a row and then copying formulas/formatting down is okay, but copying the row and inserting the copied row creates a redundant set of rules and splits the ranges in the original rule sets. This shouldn't happen, and it really needs to be fixed.
Another Issue: If you have a Table with a conditional formatting rule that depends on a different column, trying to move that column via cut/insert can crash Excel.
-
Roland
commented
Thanks for the suggestion Levi. :)
Conditional formatting is great, but it "fractures" when the sheet is edited, creating lots of separate rules. Cleaning it up is a pain, but if we could just resize the "Conditional Formatting Rules Manager" window - even maximising it onto a second screen - it would be so much easier to deal with the mess of dozens of rules fracturing into hundreds of rules!Just remove the Window size = Fixed border setting!!!!!
-
miko
commented
this would be great. i would also add the ability to search for, or the automatic highlighting of all external references in conditional formatting, as they are otherwise hard to find & manage, especially when after some editting, a very few conditional formatting rules quickly become huge list of the same rules applied to different ranges...
-
Cameron_CSA
commented
Addition functionality
- Resize the pop-p window,
- select multiple for deletion
- Not require to press F2 when editing/entering formulas to stop random ranges coming up.
- Copy an existing rule
- Enable alignment as part of the format options
- Honour alignment settings before conditional formatting is applied when a cell is included in multiple CF's rangesUsing Excel 2016 through a 365 subscription at work and the conditional formatting rules do not stack up as in previous versions. Where possible I use ALT + CTRL + V, paste special when in conditionally formatted regions.
-
Anonymous
commented
I don't know how many times I've mangled up my sheets due to the archaic and the inabilityt o customize the CF Manager. No ability to change the default paste method (the extra work through of selecting the merge conditional formatting is a chore, as well as easily forgotten, over the hundreds of cut and pastes done regularly.) The inability to resize the window? How difficult is that to add? Seeing 5 rules at a time is also archaic. Please listen to these votes, these changes would be greatly appreciated! Thanks!
-
Anonymous
commented
Like R Rutte said, let me resize the manager window and change the column widths within please, this has been bugging me for so long now. Also, something that would be really handy for me is a rule type that says: Make this cell the same colour as REFERENCE (where REFERENCE would be $A1, $A$1 etc).