Make "Paste and Merge Conditional Formatting" the Default Paste Behavior
When using Paste or Insert Copied Cells, conditional formatting rules should be merged by default. The paste behavior is currently inconsistent. For example, if you copy/paste a single cell, then rules are merged, but if you copy/paste multiple cells, then rules can become fractured or split (meaning that duplicate rules are created for the newly pasted cells).
There is already a precedent set for using merged conditional formatting as the default paste behavior: Google Sheets has been working this way for a long time and it works well.
Fractured or Split CF rules can be a serious problem. Consider the case where you are using one of the built-in data bar options to visually analyze data (see the attached screenshot). The sizes of the data bars are relative to the range of values in the Applies To field. If you insert a copied row or use other methods of copy/paste that cause the original CF rule to become fractured or split, then the relative sizes of the data bars will be messed up and lead to incorrect visual interpretations.
The "Paste and Merge Conditional Formatting" (or the equivalent option via Paste Special) is not something most users will know about. It also only merges rules when pasting within or overlapping a range (not when pasting contiguously). The rules should be merged when pasting rows to the end of a table (an example of pasting a contiguous range), or when using Insert Copied Cells to append rows to a table.
GS merges rules even if the ranges are not contiguous, meaning that if you copy A1 to C1, then the CF rule would apply to "$A$1,$C$1", and I think that should be the default behavior because it more closely models user intent (consider the data bar or color scale example).
If merging non-contiguous ranges is a problem due to a limit on the number of separate ranges that can be listed in the "Applies To" field for a rule, then perhaps the automatic merging would apply only to contiguous ranges (cells pasted within, overlapping, or adjacent to the original rules).
I use the following as a solution. If you copy (a previous entry) and paste then you MUST use the Paste Special option of 'Merge Conditional Formatting'. Problem is, if someone forgets to do this, the fragments occurs.
I have a VBA script where I have provided both a button and short cut key to do this paste.
The VBA code is based around:
Selection.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats, Operation _
:=xlNone, SkipBlanks:=False, Transpose:=False
I have some code before it to help the Copy, as well a automtically selecting the last avaiable row in my spreadsheet to do the paste - but that may not apply for you.
Good luck. Ken
Wayne Erfling commented
Presently the worst single problem I know of in Excel.
My biggest issue with fragmented CF rules is performance, though I've also seen color bars affected as noted by the initiator.
In one of my spreadsheets I was getting 15-30 seconds response time making the spreadsheet painful to use.
I've spent several HOURS arduously removing fractured rules, as performance of the Conditional Formatting maintenance dialog was affected as much as the rest of the spreadsheet.
This problem seems to have been around for several years and its well overdue for a fix.
If you setup conditional formatting on TOTAL columns, adding and removing rows doesnt change it.
Combined with use of column() and row() as part of the conditional format rules it is possible to create effects in whatever range you want.
Mounia Inoughi commented
I totally agree on the above.
I couldn't tell you how many times I've had to go back and edit conditional formatting rules because of this. It's a PITA. On the other hand, I now know conditional formatting rules inside out, which is not my main objective.
Wayne Erfling commented
I believe this issue extends beyond mere PASTEing to other forms of re-organization, for example sorting.
In any spreadsheet where I have added a significant number of conditional formatting formulas, this has become an issue.
I have seen dozens to hundreds of conditional formatting rules created by the "fracturing" process.
Just take Jon's simple example and imagine working on a range with hundreds of cells with conditional formatting for days or months.
For those of us who "really" use conditional formatting this can be a real headache.