Forum Discussion
Conditional formatting and Tables
My Excel 365 doesn't do it (I'm not convinced it used to happen with older versions either)
If I insert a row then copy and paste down the row above it does happen
How are you inserting the row? And are you doing any copy pasting after inserting the row
Wyn
MVP
- I have exactly the same issue. I have Office 365 and none of the solutions here solved the issue.
Wyn Hopkins My usual way is to right click on the row 'below' where I want to insert a new row. For example, if there is data for John Doe in row 80 and I want to add another line item for John Doe directly below that. I right click on row 81 and select 'insert' then I can copy paste values in there, or just enter them manually so John's data stays together. All my formulas in the sheet work and the cond formatting "works" as well... but as shown the cond formatting creates new rules to do this as opposed to just extending the range like I think it should to reduce the amount in there. This doesn't happen when the table expands by copying data at the bottom.
I looked at some other sheets I made too and they have been doing the same thing.
Wyn, it still inserts extra rules if references in rule formula are out of applied range, like in this case - formula uses S4 but range starts from S5.
SergeiBaklan It assure you this is not the case. I just went in and deleted that rule to check and when I inserted a row it did the same thing.
Just in case, could you please share rule formula?
There is a Copy-Paste-Special option that allows you to merge rather than duplicate the conditional formats, but I don't think it gets invoked with Table additions
