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
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!
John [MS XL]
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?
+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.
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.
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.
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.
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.
"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.
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 box
Wyn Hopkins commented
Good suggestions, especially being able to add a comment to each rule explaining the "why"
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.
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!!!!!
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...
- 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 ranges
Using 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.
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!
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).
I find that Excel 2016 freezes just when I'm using it for my checking account reconciliation. It's so frustrating to be waiting for the statement to download from the bank only to have it freeze and you have to start all over again. It freezes if I'm pasting from another page in the workbook. I've taken to saving after every transaction I record but that can sometimes take forever if the program decides it wants to play it that way. I'm considering finding a different program to use and I've used Excel for years.
Paste and Copy r: Backup FC, cleanner for FC range
Kenneth Barber commented
I have another one to add. If you apply conditional formatting to all cells, it will show the notation 1:1048576, which is all rows. I feel like "all cells" should have its own notation so then it still applies to all cells even if you delete a row.