Keep conditional formatting range when inserting/deleting cells/rows/columns?




I sometimes use conditional formatting. For each entry, there's a cell range that it applies to. Often I need it to be used on the entire sheet, or at least a large range of it, i.e. all rows that have content.


But then I need to insert or remove data, and that mucks up the range. It seems to copy the entry to the range above, the inserted range, and the range below. I want it to keep the range and entries as they are when I edit the sheet's data.



I usually enter the range as e.g. $1:$1048576 or $A:$XFD.

6 Replies
If you first copy an entire row before inserting a new one (Insert copied row) I expect this won't happen. Otherwise, I feel your pain. The CF rules manager is one of the most unusable interfaces Excel has.

That doesn't work in Excel 2016.  I'm wondering if there is a way to set the "Applies to" field to a named range vs. $A:$A.

Not that I know of, Excel manages that list and I expect inserting a row will override whatever range name you put there.
Hi guys

Adding a name range should work allowing you to add or delete rows without affecting your formulas.

Just make sure that any formula refers to the name range you have specified.


This doesn't work - when a named range is referred to, it immediately evaluates it, so using a named range is equivalent to entering the definition of said range.


One thing I've found in my spreadsheet is that inserting a row only affects one conditional formatting entry (probably because this is the only one which uses a formula, one which refers to other surrounding cells). Nonetheless this is annoying as the 'Manage Rules' dialog is difficult enough to use (you can't even resize?!) without rules being spawned with every spreadsheet edit.

This is the most frustrating issue with Excel that I'm having. We use Excel 365 for work and keep tabs on the current progress of repairs. We constantly need to insert, delete, and rearrange rows. Every time we do, we have to fix the conditional formatting range. Please help if anyone knows how to get around this without manual intervention each and every time.