Excel – Why is Excel butchering the Conditional Formatting

microsoft-excel-2010worksheet-function

I have this conditional formatting set

enter image description here

but when I move/delete/copy/cut/paste/insert a cell/row/column my conditional formatting becomes like this

enter image description here

The rules have duplicated themselves, the "Applies to" now is broken up and from the little bit you can see one of my blue rules has an altered Rule.

Sometimes I make mistakes when making an Excel spreadsheet requiring me to move and or delete stuff from a single cell to entire rows. Why does Excel think it's necessary to butcher my conditional formatting ? Especially since by creating these duplicate rules it gives me more work to do if I need to refine them later on which is what I use conditional formatting to avoid doing i.e. more work

Best Answer

This happens when you Cut/Copy and then Paste lines within the applied range, in Excel 2000, 2003, 2007, and 2010 (I don't know about others).

it is still fully functional; if you consider then first and fifth line shown, they together define the complete range (just stupidly split), and the formula is identical for both (note that each formula is shown relative to the first cell it applies to; as the formula in the fifth line applies to $D$74, it contains ...$D$74... in it).

I have not found a way (looking for several years) to change that behavior. Whenever you copy or cut and then insert lines several times, Excel cuts the respective conditional formulas' ranges in pieces like this.

A manual workaround (to repair the formulas) is to

  1. Select all lines except the first, chose 'Clear Rules from Selected Cells'. You will now have your conditional formatting only in the first line.
  2. Open the Conditional Formatting manager, chose 'This worksheet', and extend all formulas to all lines (by typing :$nnnn over :$2).
  3. Yes you will have to repeat that ever so often. As I said, I have not found a better solution in years.
Related Question