Excel and Conditional Formatting Mess (with Tables)

[NewYears1978]

I have a table, I have conditional formatting in it. It works great.

Later I go back into my conditional formatting and Excel has duplicated the conditional formatting over and over and it just gets really wonky. I have never been able to figure out why it does this.

I simple have a table that conditionally formats a whole row based on text in the last column. I use this

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Next time I go in there there are multiple duplicated formulas, renamed to like all the cells int he table (instead of just staying A:L) and i dunno why. Things start to act wonky too. Is this just normal Excel behavior?

Look at my example image, the orange formula on bottom, I originally typed that as $A:$L and now it says that (no columns were added to the table), then the top orange one you see where it says $I$4... this isn't even something I entered..where did it come from...

Help!

[humdingaling]

Later I go back into my conditional formatting and Excel has duplicated the conditional formatting over and over and it just gets really wonky. I have never been able to figure out why it does this.

once you start cutting/pasting/deleting/move/etc everywhere the conditional format remove from original and add a replicated new one

so you will always have to go back in and clean up your lists
its a down side of using conditional format on a working document
its good sometimes but bad many of other times
to circumvent, you can use WHOLE row/column references..(not always possible) or use named ranges

or have VBA reapply all the codes all the time...
or just replying them manually
whatever you choose you need to do something proactively to work around it

i often use the manual methods because...well i just want to fix immediately and move on
all other methods would require premeditated solutions

[NewYears1978]

I normally don't move, cut, paste once I get everything set, I think you may be right though I may have Converted my table to a range, added some columns or something and it fouled up.

I converted my table back to a range, removed all formatting, rewrote my conditional formatting and now it seems to be staying in tact

[FDibbins]

CF has a nasty habit of copying/duplicating rules if you copy/paste cells that contain a rule (often not wrong, just duplicated). It is always best to check, if you change things around

[NewYears1978]

Yeah what a mess, and you end up with 15 duplicates..and there's no simple way to fix it...

[FDibbins]

nope, other that deleting the dups. Start at the bottom and work up, then you dont have to move the cursor as much

[NewYears1978]

Yeah what a pain! They really need to work on that aspect of excel. They don't always expand with table properly either it's super annoying.

[FDibbins]

I agree, that problem has been around for a number of versions now.
Thanks for the feedback

[NewYears1978]

I assume you could probably write conditional formatting with VBA instead that uses the table for data..but would probably require rerunning a macro and blah.

I've just left it how it is haha and moved on. Weird that Microsoft doesn't even fix issues that have been around for a long time. There are several that I deal with like this.