Conditional formatting rules split and repeated

Question

I have the problem that Excel 2010 splits the rules when copying and pasting a complete row with Conditional Formatting. Example:

1. Create a simple conditional rule like =A1 <> "" (if true, color the cell red) and assign the rule to the whole column A $A:$A.
2. Select the complete row 10, copy and paste it as row 11 in the same sheet.
   ==> Excel splits the rule into two rules:
   =A1 <> "" Applies to $A$1:$A$10;$A$12:$A$1048576 and
   =A1 <> "" Applies to $A$11.

If the row is selected then the new row is inserted, without copying the current one, the rule is not split into two (still applied to $A:$A), the new row has the conditional formatting too.

I'm bound to insert new rows by copying/pasting an existing one because some of the cells contain formulas which must be copied too. So that is the easiest way to insert a new row.

Imagine 100 rows are inserted that way, then Excel will create 100 rules for the same rule. Thus the conditional formatting list is getting very unclear. A change to the formatting rule would be impossible.

How can I tell Excel that it should not split the rules when copying and pasting an existing row? Is there a way to disable this "feature"?

Answer 1

When pasting choose Paste Special and check All merging conditional formats. Some details here.