How to stop Excel from combining Conditional Formatting rules into 1 broken rule

Question

I understand that Conditional Formatting is a horrendous tool, much like Excel itself, but it's the only realistic option so I'm stuck with using it.

However, I'm unable to grasp how painfully stupid this bug is, so hopefully someone can help.

I have price tiers in columns in 3 different currencies, and some of the cells need to be formatted to 4 decimal places instead of 2. This is determined by a cell on each row.

I have 3 conditions set up, and they are working perfectly. Perfectly, that is, until I have the audacity to delete a row, or paste something in to a row, at which point Excel decides that I'd instead prefer my 3 rules to be condensed into a single rule that encompasses all of the columns, but only applies a single currency.

Before Deleting a Row (etc.) = Working as Intended

After Deleting a Row (etc.) = Merged for no logical reason; no longer works

This is ridiculous, and I can't find a way to stop it happening.

I can see several people mentioning similarly stupid problems over the years, but I don't see any solutions that have helped me, or any evidence that suggestions have helped anyone else actually fix their problems either.

Thanks in advance.

Answer 1

This issue seems to be reproducible, and is a bug that was already reported several years ago by some users (see Conditional formatting rules delete/break/merge when deleting columns/rows RRS feed).

Excel seems to be thinking (erroneously) that those 3 formattings are all the same format, so it combines the rules (when a row is deleted Excel checked the Conditional Formatting rules to see if something within the rule was deleted/moved, and it needs to update it, therefore it is then when it makes this error and not before that.)

The workaround, suggested there, works very well and might be a good option for you:

• Besides the Number Format you are setting (Currency Symbol) also add to each rule another formatting, for example a different cell Fill Color to of the rules.

The reason this works is that the lapse in Excel's thinking is only for the Number formattings, so if you do any other formatting Excel now knows that those rules are not the same and cannot be combined.