Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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).

46 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jon Wittwer shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    10 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • FaceTheMusic commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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
        ws.Activate
        Cells.FormatConditions.Delete
        ''Every Other Row Tan
        Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
        Range("A4:M203").FormatConditions(Range("A4:M203").FormatConditions.Count).SetFirstPriority
        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.AddUniqueValues
        Columns("B").FormatConditions(Columns("B").FormatConditions.Count).SetFirstPriority
        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
        End If
        Next

        starting_ws.Activate 'activate the worksheet that was originally active
        Application.ScreenUpdating = True

        End Sub

      • Hema commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        This problem seems to have been around for several years and its well overdue for a fix.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      Feedback and Knowledge Base