SOLVED
Home

Copy conditional formatting for several rows

%3CLINGO-SUB%20id%3D%22lingo-sub-806116%22%20slang%3D%22en-US%22%3ECopy%20conditional%20formatting%20for%20several%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806116%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%2020%2B%20cells%2Fcolumns%20per%20row%20with%20different%20values%20and%20I've%20added%20one%20column%20giving%20the%20average%20value%20per%20row.%26nbsp%3B%3CBR%20%2F%3EThereafter%20I've%20used%20conditional%20formatting%20giving%20the%20individual%20cells%20red%20or%20green%20colour%20if%20it%20is%26nbsp%3B%20below%20or%20above%20the%20average%20number%20per%20row.%20It%20is%20easy%20to%20arrange%20this%20row%20by%20row%2C%20but%20having%20hundreds%20of%20rows%20I%20want%20to%20copy%20the%20formula%20for%20the%20conditional%20formating%20with%20colours%20to%20all%20rows.%3CBR%20%2F%3EE.g.%20B2%20to%20X2%20gives%20an%20average%20of%20130%20which%20I%20show%20in%20cell%20Y.%20If%20C2%2C%20D2%2C%20E2...%20is%20above%20130%20%3D%20green.%20Below%20130%20%3D%20red.%20etc%3C%2FP%3E%3CP%3ENow%2C%20B3%20to%20X3%20gives%20an%20average%20of%20e.g.%2095...%20B4%20to%20X4%2C%20average%20e.g.%2045...and%20so%20on.%3C%2FP%3E%3CP%3EWhen%20just%20copying%20the%20formatting%20for%20row%20no%202%2C%20it%20relates%20to%20the%20average%20on%20row%202.%3C%2FP%3E%3CP%3EI%20want%20this%20to%20relate%20per%20row%202%2C%203%2C%204...etc%20with%20their%20individual%20average%20number%20per%20row%2C%20without%20doing%20this%20manually%20row%20by%20row%2C%20but%20copying%20the%20rule%20to%20be%20valid%20for%20all%20my%20hundreds%20of%20rows%2C%20but%20relating%20to%20the%20individual%20row-average.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20was%20just%20one%20number%20to%20relate%20to%2C%20it's%20of%20course%20easy%2C%20but%20I%20need%20it%20to%20follow%20row%20by%20row.%3CBR%20%2F%3EAnyone%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-806116%22%20slang%3D%22en-US%22%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-806169%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20conditional%20formatting%20for%20several%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392723%22%20target%3D%22_blank%22%3E%40Anders190210%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20a%20rule%20with%20the%20formula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DB2%26gt%3B%24Y2%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20entire%20range%2C%20let%20say%20%24B%242%3A%24X%241000%3C%2FP%3E%0A%3CP%3EOne%20rule%20per%20color%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806513%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20conditional%20formatting%20for%20several%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806513%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%3BGreat%2C%20thanks%20Sergei!%20%3A)%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806517%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20conditional%20formatting%20for%20several%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392723%22%20target%3D%22_blank%22%3E%40Anders190210%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Anders190210
New Contributor

Hi, I have 20+ cells/columns per row with different values and I've added one column giving the average value per row. 
Thereafter I've used conditional formatting giving the individual cells red or green colour if it is  below or above the average number per row. It is easy to arrange this row by row, but having hundreds of rows I want to copy the formula for the conditional formating with colours to all rows.
E.g. B2 to X2 gives an average of 130 which I show in cell Y. If C2, D2, E2... is above 130 = green. Below 130 = red. etc

Now, B3 to X3 gives an average of e.g. 95... B4 to X4, average e.g. 45...and so on.

When just copying the formatting for row no 2, it relates to the average on row 2.

I want this to relate per row 2, 3, 4...etc with their individual average number per row, without doing this manually row by row, but copying the rule to be valid for all my hundreds of rows, but relating to the individual row-average.  

If it was just one number to relate to, it's of course easy, but I need it to follow row by row.
Anyone? 

 

 

3 Replies
Solution

@Anders190210 

You may apply a rule with the formula like

=B2>$Y2

to entire range, let say $B$2:$X$1000

One rule per color

@Sergei Baklan Great, thanks Sergei!

@Anders190210 , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies