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

5 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Wayne ErflingWayne 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