Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.