Forum Discussion
Keep conditional formatting range when inserting/deleting cells/rows/columns?
Was having the same issue and so my search led to this forum, searched everywhere else and couldn't find the answer, played around with it and finally figured it out. It's actually a pretty simple solution.
Instead of Inserting a column or copying and inserting a column, all you have to do is select the column cells that you want to extend, then at the bottom of the cursor where the plus sign is, click and drag to the right as many columns as needed.
The CF range extended to the last column without creating any extra conditions or messing up the original range.
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.
- 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.
Cheers
DamienThis 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.
