Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Often, I create a sheet with conditional formatting, and set the cell ranges so that the conditional formatting rules are only applied once to a range of cells, e.g.

Make $A$1:$A$30 red and
Make $B$1:$B$30 blue.

After inserting/deleting a number of rows and/or columns the conditional formatting rule set becomes very fragmented, with the same rules repeated for a different ranges. e.g.

Make $A$1:$A$2 red 
Make $A$3:$A$4 red 
Make $A$5:$A$9 red
Make $A$10:$A$20 red 
Make $A$21:$A$30 red
Make $B$1:$B$2 blue 
Make $B$3:$B$4 blue
Make $B$5:$B$9 blue
Make $B$10:$B$20 blue 
Make $B$21:$B$30 blue

Is there a good practice way of preventing this, or am I doomed to cleaning up the rule sets manually when they get too messy?

share|improve this question

Inserting and deleting rows does not cause conditional formatting to get fragmented.

The cause is copy/pasting between cells or rows using the standard copy/paste. The fix is to always use paste-value or paste-formula. On the destination right click and the Paste Options section will offer 123 (values) and f (formulas). Don't copy/paste formatting as that causes the conditions to get copy/pasted and sometimes they will be fragmented.

When you do a standard copy/paste it also copies the cell's conditional formulas. Let's say you have two rules:
1) Make $A$1:$A$30 red
2) Make $B$1:$B$30 blue
Now select A10:B10 and copy/paste that to A20:B20. What Excel will do is to delete the conditional formatting for A20:B20 from the rules that applied to those cells and add new rules that have the formatting for A20:B20. You end up with four rules.
1) Make =$A$20 red
2) Make =$B$20 blue
3) Make =$A$1:$A$19,$A$21:$A$30 red
4) Make =$B$1:$B$19,$B$21:$B$30 blue
Had you copy/pasted just A10 to A20 Excel would have noticed the same rule applied to both the source and destination and does not fragment the rules. Excel is not smart enough to figure out how to avoid fragmentation when your copy/paste impacts two or more conditional formats.

Inserting and deleting rows does not cause fragmentation as Excel simply expands or shrinks the condition rule(s) that cover the area where you did the row insert or delete.

Someone suggested using $Q:$Q rather than $Q$1:$Q$30. That does not help and you will still get fragmentation when you copy/paste cell formatting as noted above.

share|improve this answer

Had the same problem when applying conditional format to a column of table. When adding rows, I found it works best to apply the rule to the entire column using $A:$A, or whichever column.

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .