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 found out this is not an issue in Excel 2007. I can copy and paste multiple cells and conditional formatting rules are merged by default. Please bring back this 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.
Darryl Pinto commented
I have seen and worked with this headache. However, I have noticed that while my coworkers experience this conditional formatting split, I do not. I am not sure why. I am running Windows 10, Excel 2016 64-bit version 16.0.4738.1000. Did this get fixed in Excel? Is there a Windows update for this? I do not know.
But since my coworkers still experience this problem, I have created a solution.
I agree with what has been posted previously; copy and paste values (or paste formulas) will totally work to not split up the conditional formatting.
I'm a little lazy for that. And I don't want those who use my spreadsheets to have to do that. I'm also not entirely confident that they will remember to do copy and paste values. :(
I don't know if this solution will work for your needs, but I resorted to deleting all conditional formatting and reapplying the correct conditional formatting every time the workbook is opened.
Because this macro runs every time the workbook is opened, the user does not need to change the way they copy and paste. They don't need to know that the macro is even there. They don't need to manually run the macro; it is automatic. I feel this creates a better user experience.
Please keep in mind that this code needs to be copied and pasted into the "This Workbook" module; not a regular module.
Private Sub Workbook_Open()
'This will delete all conditional formatting and reapply the conditional formatting properly.
'After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
'weeks there are so many conditional formattings that Excel crashes and has to recover.
Dim ws As Worksheet, starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "InvErr" Then
''Every Other Row Tan
Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
Range("A4:M203").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
Range("A4:M203").FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
Range("A4:M203").FormatConditions(1).Interior.TintAndShade = 0
Range("A4:M203").FormatConditions(1).StopIfTrue = False
''Highlight Duplicates Red
Columns("B").FormatConditions(1).DupeUnique = xlDuplicate
Columns("B").FormatConditions(1).Font.Color = -16383844
Columns("B").FormatConditions(1).Font.TintAndShade = 0
Columns("B").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
Columns("B").FormatConditions(1).Interior.Color = 13551615
Columns("B").FormatConditions(1).Interior.TintAndShade = 0
Columns("B").FormatConditions(1).StopIfTrue = False
starting_ws.Activate 'activate the worksheet that was originally active
Application.ScreenUpdating = True
Hope there is an option where you can do paste special and bring all conditional formatting values/rules, so don't have recreate rules again.
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.