Home

Conditional Formatting on whole column with variable reference cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1005636%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1005636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EHi%2C%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EI'd%20like%20to%20format%20the%20colour%20fill%20of%20multiple%20cells%20within%20one%20particular%20column%2C%20when%20it's%20value%20is%20less%20than%20a%20cell%20in%20the%20row%20above%20of%20the%20next%20column.%20how%20should%20I%20do%20this%20please%3F%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Eeg.%20when%20the%20value%20of%20D93%20is%20smaller%20than%20the%20value%20of%20E92%20format%20fill%20red.%20Apply%20to%20all%20D's.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ES%3CFONT%3Eo%20far%20I%20have%20established%20that%20if%20I%20type%20%3D(%24D%2493%26lt%3B%24E%2492)%20then%20I%20can%20make%20the%20rule%20work%20for%20cell%20D93.%20But%20how%20can%20I%20replicate%20this%20formula%20throughout%20the%20whole%20of%20the%20D%20column%2C%20without%20typing%20for%20each%20individual%20cell.%20Is%20this%20even%20possible%3F%20Is%20there%20a%20short%20cut%20to%20copying%20the%20rules%20over%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThankyou%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1005636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EColumn%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1005680%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1005680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F456629%22%20target%3D%22_blank%22%3E%40LazyBee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemove%20the%20dollar%20signs%20and%20then%20Copy%2C%20Paste%20Formats%20down%20the%20range%20you%20need%20the%20format%20to%20be%20applied%20to%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1006120%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1006120%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20applied%20your%20suggestion%20and%20it%20has%20worked%20majoritably%2C%20however%20there%20are%20a%20few%20that%20are%20inaccurate%2C%20i.e.%20same%20value%20or%20higher%20value%20any%20ideas%20why%20this%20is%20please%20see%20attached%20photo%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F156895i3EBBE9AC740A6650%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-11-13.png%22%20title%3D%222019-11-13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20why%20this%20is%20happening%2C%20please%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThankyou%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1006702%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1006702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F456629%22%20target%3D%22_blank%22%3E%40LazyBee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESince%20you%20apply%20CF%20to%20entire%20column%20D%2C%20your%20formula%20shall%20be%20for%20the%20first%20cell%20of%20the%20range%2C%20other%20words%20for%20D1%2C%20otherwise%20the%20logic%20will%20be%20shifted.%20I'd%20suggest%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24D1%3COFFSET%3E%3C%2FOFFSET%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20498px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F156971iA25E75D73FC17100%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F156973i379BC985FBEA7092%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1008289%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1008289%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%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EHmm%2C%20this%20seems%20to%20have%20complicated%20things%20slightly.%20I%20applied%20the%20example%20given%20to%20cell%20D5%20%22%3CFONT%3E%3D%24D5%3COFFSET%3E%22%20and%20now%20the%201st%20cell%20is%20filled%20red.%3C%2FOFFSET%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157187iDB75F30D67CBABC8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-11-14%20(2).png%22%20title%3D%222019-11-14%20(2).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20understandable%20as%20E4%20has%20letters%20not%20numbers%2C%20but%20it%20hasn't%20resolved%20the%20original%20problem.%20Infact%20it%20seems%20to%20have%20delivered%20a%20new%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157189i46029B8CE42B9F12%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-11-14%20(1).png%22%20title%3D%222019-11-14%20(1).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20change%20the%20value%20(highlighted%20Yellow)%20to%20trigger%20the%20formatting%20it%20actually%20changes%20D3!%3F%3C%2FP%3E%3CP%3EDid%20I%20miss%20something%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1009751%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1009751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F456629%22%20target%3D%22_blank%22%3E%40LazyBee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20in%20this%20variant%20we%20apply%20conditional%20formatting%20to%20entire%20column%20D%2C%20not%20to%20concrete%20cell%20or%20range.%3C%2FP%3E%0A%3CP%3ETo%20exclude%20headers%20we%20may%20modify%20rule%20formula%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(%24D1%3COFFSET%3E5)%3C%2FOFFSET%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWhen%20it'll%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20736px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157273iE20BF96039826ACE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1009975%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1009975%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%3EPERFECT!%20Thankyou%20so%20much%2C%20really%20appreciate%20the%20additional%20time%20spent%20on%20helping%20me%20clarify%20my%20oversight%2C%20Thanks!%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012115%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20whole%20column%20with%20variable%20reference%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F456629%22%20target%3D%22_blank%22%3E%40LazyBee%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
LazyBee
Occasional Contributor

Hi,

I'd like to format the colour fill of multiple cells within one particular column, when it's value is less than a cell in the row above of the next column. how should I do this please?

 

eg. when the value of D93 is smaller than the value of E92 format fill red. Apply to all D's.

 

So far I have established that if I type =($D$93<$E$92) then I can make the rule work for cell D93. But how can I replicate this formula throughout the whole of the D column, without typing for each individual cell. Is this even possible? Is there a short cut to copying the rules over?

 

Thankyou

7 Replies

@LazyBee 

Remove the dollar signs and then Copy, Paste Formats down the range you need the format to be applied to .

Thanks @Riny_van_Eekelen 

 

I've applied your suggestion and it has worked majoritably, however there are a few that are inaccurate, i.e. same value or higher value any ideas why this is please see attached photo:

 

2019-11-13.png

 

 

Any ideas why this is happening, please? 

Thankyou

@LazyBee 

Since you apply CF to entire column D, your formula shall be for the first cell of the range, other words for D1, otherwise the logic will be shifted. I'd suggest

=$D1<OFFSET($D1,-1,1)

image.png

and

clipboard_image_0.png

@Sergei Baklan Hmm, this seems to have complicated things slightly. I applied the example given to cell D5 "=$D5<OFFSET($D5,-1,1)" and now the 1st cell is filled red.

 

2019-11-14 (2).png

 

 

This is understandable as E4 has letters not numbers, but it hasn't resolved the original problem. Infact it seems to have delivered a new one:

 

2019-11-14 (1).png

 

When I change the value (highlighted Yellow) to trigger the formatting it actually changes D3!?

Did I miss something?

 

 

Thanks for your help

 

@LazyBee 

First, in this variant we apply conditional formatting to entire column D, not to concrete cell or range.

To exclude headers we may modify rule formula as

=($D1<OFFSET($D1,-1,1))*(ROW()>5)

When it'll be like

image.png

Please check in attached file.

@Sergei BaklanPERFECT! Thankyou so much, really appreciate the additional time spent on helping me clarify my oversight, Thanks!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Discussion - Updating our interface with Fluent touches
Elliot Kirk in Discussions on
102 Replies