Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-127962%22%20slang%3D%22en-US%22%3EKeep%20conditional%20formatting%20range%20when%20inserting%2Fdeleting%20cells%2Frows%2Fcolumns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-127962%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sometimes%20use%20conditional%20formatting.%20For%20each%20entry%2C%20there's%20a%20cell%20range%20that%20it%20applies%20to.%20Often%20I%20need%20it%20to%20be%20used%20on%20the%20entire%20sheet%2C%20or%20at%20least%20a%20large%20range%20of%20it%2C%20i.e.%20all%20rows%20that%20have%20content.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20then%20I%20need%20to%20insert%20or%20remove%20data%2C%20and%20that%20mucks%20up%20the%20range.%20It%20seems%20to%20copy%20the%20entry%20to%20the%20range%20above%2C%20the%20inserted%20range%2C%20and%20the%20range%20below.%20I%20want%20it%20to%20keep%20the%20range%20and%20entries%20as%20they%20are%20when%20I%20edit%20the%20sheet's%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20usually%20enter%20the%20range%20as%20e.g.%20%241%3A%241048576%20or%20%24A%3A%24XFD.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-127962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econditional%20formating%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformating%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320340%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20conditional%20formatting%20range%20when%20inserting%2Fdeleting%20cells%2Frows%2Fcolumns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320340%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20doesn't%20work%20-%20when%20a%20named%20range%20is%20referred%20to%2C%20it%20immediately%20evaluates%20it%2C%20so%20using%20a%20named%20range%20is%20equivalent%20to%20entering%20the%20definition%20of%20said%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20thing%20I've%20found%20in%20my%20spreadsheet%20is%20that%20inserting%20a%20row%20only%20affects%20one%20conditional%20formatting%20entry%20(probably%20because%20this%20is%20the%20only%20one%20which%20uses%20a%20formula%2C%20one%20which%20refers%20to%20other%20surrounding%20cells).%20Nonetheless%20this%20is%20annoying%20as%20the%20'Manage%20Rules'%20dialog%20is%20difficult%20enough%20to%20use%20(you%20can't%20even%20resize%3F!)%20without%20rules%20being%20spawned%20with%20every%20spreadsheet%20edit.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-148604%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20conditional%20formatting%20range%20when%20inserting%2Fdeleting%20cells%2Frows%2Fcolumns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-148604%22%20slang%3D%22en-US%22%3EHi%20guys%3CBR%20%2F%3E%3CBR%20%2F%3EAdding%20a%20name%20range%20should%20work%20allowing%20you%20to%20add%20or%20delete%20rows%20without%20affecting%20your%20formulas.%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20make%20sure%20that%20any%20formula%20refers%20to%20the%20name%20range%20you%20have%20specified.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3EDamien%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-142431%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20conditional%20formatting%20range%20when%20inserting%2Fdeleting%20cells%2Frows%2Fcolumns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-142431%22%20slang%3D%22en-US%22%3ENot%20that%20I%20know%20of%2C%20Excel%20manages%20that%20list%20and%20I%20expect%20inserting%20a%20row%20will%20override%20whatever%20range%20name%20you%20put%20there.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-142136%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20conditional%20formatting%20range%20when%20inserting%2Fdeleting%20cells%2Frows%2Fcolumns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-142136%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20doesn't%20work%20in%20Excel%202016.%26nbsp%3B%20I'm%20wondering%20if%20there%20is%20a%20way%20to%20set%20the%20%22Applies%20to%22%20field%20to%20a%20named%20range%20vs.%20%24A%3A%24A.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-128025%22%20slang%3D%22en-US%22%3ERe%3A%20Keep%20conditional%20formatting%20range%20when%20inserting%2Fdeleting%20cells%2Frows%2Fcolumns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-128025%22%20slang%3D%22en-US%22%3EIf%20you%20first%20copy%20an%20entire%20row%20before%20inserting%20a%20new%20one%20(Insert%20copied%20row)%20I%20expect%20this%20won't%20happen.%20Otherwise%2C%20I%20feel%20your%20pain.%20The%20CF%20rules%20manager%20is%20one%20of%20the%20most%20unusable%20interfaces%20Excel%20has.%3C%2FLINGO-BODY%3E
Kjell Rilbe
Occasional Visitor

Hi,

 

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.

 

How?

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

5 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.

Cheers
Damien

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.