Conditional formatting and Tables

%3CLINGO-SUB%20id%3D%22lingo-sub-2079215%22%20slang%3D%22en-US%22%3EConditional%20formatting%20and%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079215%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20conditional%20formatting%20set.%20When%20I%20add%20info%20to%20the%20bottom%20the%20table%20expands%20and%20so%20does%20the%20conditional%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20insert%20a%20row%20into%20the%20middle%20of%20the%20table%20however%2C%20The%20formatting%20seems%20to%20expand%2C%20but%20it%20does%20so%20by%20creating%20more%20formatting%20rules.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuch%20as%3A%3C%2FP%3E%3CP%3EApplies%20to%20-%20%24A%241%3A%24A%24100%20turns%20into%3C%2FP%3E%3CP%3E%24A%241%3A%24A%2489%20and%20%24A%2490%20and%20%24A%2491%3A%24A%24101%3C%2FP%3E%3CP%3EThis%20makes%20for%20a%20larger%2C%20messier%20list%20of%20Conditional%20formatting%20rules.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20get%20around%20this%20to%20keep%20it%20cleaner%3F%20I%20use%20Office%202016%2C%20do%20upgraded%20versions%20not%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2079215%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2081678%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20and%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2081678%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F525715%22%20target%3D%22_blank%22%3E%40kevin_serenity%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20Excel%20365%20doesn't%20do%20it%26nbsp%3B%20(I'm%20not%20convinced%20it%20used%20to%20happen%20with%20older%20versions%20either)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20insert%20a%20row%20then%20copy%20and%20paste%20down%20the%20row%20above%20it%20does%20happen%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20are%20you%20inserting%20the%20row%3F%26nbsp%3B%20And%20are%20you%20doing%20any%20copy%20pasting%20after%20inserting%20the%20row%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWyn%3C%2FP%3E%0A%3CP%3EMVP%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2081948%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20and%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2081948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20Copy-Paste-Special%20option%20that%20allows%20you%20to%20merge%20rather%20than%20duplicate%20the%20conditional%20formats%2C%20but%20I%20don't%20think%20it%20gets%20invoked%20with%20Table%20additions%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CharlesDecisionModels_0-1611228952038.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248016i29C808C836FF35BC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22CharlesDecisionModels_0-1611228952038.png%22%20alt%3D%22CharlesDecisionModels_0-1611228952038.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2082139%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20and%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWyn%2C%20it%20still%20inserts%20extra%20rules%20if%20references%20in%20rule%20formula%20are%20out%20of%20applied%20range%2C%20like%20in%20this%20case%20-%20formula%20uses%20S4%20but%20range%20starts%20from%20S5.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2082861%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20and%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...actually%20you%20might%20be%20right.%20I%20deleted%20the%20two%20formula%20rules%20that%20had%20to%20do%20with%20matching%20one%20row%20to%20another.%20Once%20I%20inserted%20another%20row%20that%20didn't%20affect%20any%20of%20the%20other%20rules%20in%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a table with conditional formatting set. When I add info to the bottom the table expands and so does the conditional formatting.

 

When I insert a row into the middle of the table however, The formatting seems to expand, but it does so by creating more formatting rules.

 

Such as:

Applies to - $A$1:$A$100 turns into

$A$1:$A$89 and $A$90 and $A$91:$A$101

This makes for a larger, messier list of Conditional formatting rules. 

 

Is there a way to get around this to keep it cleaner? I use Office 2016, do upgraded versions not do this?

 

Thank you!

11 Replies

Hi @kevin_serenity 

 

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

@Wyn Hopkins 

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

CharlesDecisionModels_0-1611228952038.png

 

@Wyn Hopkins 

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.

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

@Sergei Baklan 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.

@kevin_serenity 

Just in case, could you please share rule formula?

@Sergei Baklan 

Here's a couple of them, pretty basic rules

=$AA6<>"" applies to =$AA$6:$AA$93

=AND($E6="",$F6="") Applies to =$E$6:$F$93,$U$6:$Y$93

 

These I used to indicate if two opposing numbers cancel each other out

=AND($T6<>"",$T7<>"",$T6=$T7*-1) Applies to =$T$6:$T$93

=AND($T5<>"",$T6<>"",$T5=$T6*-1) Applies to =$T$6:$T$93

 

I feel like the issue only has to do with the 'applies to' part and inserting a row into the middle of the table makes excel do a kind of workaround to get it to apply to the whole range. Very strange though

@Sergei Baklan 

...actually you might be right. I deleted the two formula rules that had to do with matching one row to another. Once I inserted another row that didn't affect any of the other rules in the sheet.

@kevin_serenity 

Thank you for the formulas. Instead of $T5<>"" you may use something like =OFFSET($T6,-1,0)<>"". I didn't test but perhaps that could help.

@kevin_serenity 

 

I'd recommend "surfacing" any slightly complex conditional formatting formulas into the table itself if at all possible (you can then use the Group option to hide these helper columns)

 

Easier to debug that way.

 

image.png

 

Also I tried to avoid Offset.  Although it is a nice suggestion from Sergei

Personally I'd use this  (more complex but not volatile)

 

=IFERROR(   [@item] = INDEX([item], ROW([@item])-ROW(Table1[[#Headers],[item]])+1), "")

image.png

@Wyn Hopkins , I agree, it's always better to avoid volatile functions. On the other hand it depends on situation. OFFSET() is more user-friendly. Depends on workbook and my skills I may save few milliseconds on recalculations but lose hours on solution development and its maintenance.