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

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.

Highlighted
Not that I know of, Excel manages that list and I expect inserting a row will override whatever range name you put there.
Highlighted
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
Highlighted

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.

Related Conversations
Documentation Migration
SunLeo in Office 365 on
0 Replies
Restrict Edit access on columns on user/group basis.
Vimmi Rawat in SharePoint on
0 Replies
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
How to SUM Data from non-regular rows
Andy_Przybysz in Excel on
1 Replies