10

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

Dave M
  • 13,250
Memor-X
  • 625

3 Answers3

5

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.
Aganju
  • 10,161
0

I have the same trouble. My spreadsheet is a spending tracker and I have about 25 different formatting rules based on spending categories. Sometimes my formatting rules blow out to nearly 200 with numerous duplications of my original rules.

The quickest work around I have found to clear all the unneccessary rules is to create a template worksheet first which contains "clean & correct" formatting rules only.

Then in the offending worksheet I go to the Home tab > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Back to the template worksheet, select the entire worksheet, click on the Format Painter.

Switch to the cleared worksheet and paste/paint into cell A1.

The reason your formula has split itself like that is because you likely/probably/possibly inserted new lines at rows 73 and 74 and so the formatting isn't applied there. The formula then picks up at row 75 and continues until the extreme edge of the worksheet (row 1,048,576).

If you set up a template where the formatting is $A:$D and then follow my steps above to clean and reapply your styles, it should work correctly. Hope that helps you after all these years!

Bel_D
  • 1
0

And this still happens on browser-based Excel. In fact, this should be considered as a bug, as this makes a mess on existing conditional format.

Amitie 10g
  • 1