Home

Relative conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-804815%22%20slang%3D%22en-US%22%3ERelative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-804815%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20multiple%20worksheets%20in%20Excel%20365%20that%20present%20data%20from%20one%20month%20to%20another.%20I%20am%20trying%20to%20make%20the%20data%20turn%20green%20if%20it's%20gone%20up%20since%20the%20previous%20month%20or%20red%20if%20it's%20gone%20down.%20I%20would%20like%20to%20know%20the%20quickest%20way%20to%20copy-paste%20the%20conditional%20formatting%20so%20it%20is%20relative%20to%20the%20previous%20month%20and%20not%20reference%20the%20original%20cell%20in%20the%20formula.%20ex%3A%20I%20have%20sales%20in%20column%20A2%20to%20A50%20in%20worksheet%20february%20and%20would%20like%20to%20format%20it%20n%20relation%20to%20column%20A2%20to%20A50%20in%20worksheet%20january%20while%20matching%20A2%20feb%20with%20A2%20jan%2C%20A3%20feb%20with%20A3%20jan...%20I%20hope%20I'm%20being%20clear.%20If%20it's%20easier%20with%20Icons%2C%20I%20can%20do%20that%20instead.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-804815%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806664%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806664%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20February%2C%20you%20could%20use%20conditional%20formats%20with%20these%20formulas%3A%3CBR%20%2F%3E(RED)%3CBR%20%2F%3E%3DA2%3CJANUARY%3E%3C%2FJANUARY%3E(GREEN)%3CBR%20%2F%3E%3DA2%26gt%3BJanuary!A2%3CBR%20%2F%3E%3CBR%20%2F%3EMake%20sure%20to%20apply%20the%20conditional%20formats%20to%20your%20necessary%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20for%20each%20month%20that%20follows%20just%20simply%20change%20%22January%22%20to%20the%20previous%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806753%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806753%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works%20but%20how%20do%20I%20copy%20paste%20it%20to%20the%20rest%20of%20my%20cells%20without%20it%20comparing%20to%20A2%20but%20to%20the%20following%20cells%20automatically.%20If%20I%20want%20to%20compare%20A2%20(january)%20with%20A2%20(february)%2C%20A3%20(january)%20with%20A3%20(february)%20and%20so%20on%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%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-806773%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806773%22%20slang%3D%22en-US%22%3EMake%20sure%20that%20the%20conditional%20format%20formula%20uses%20relative%20references%20(i.e.%20no%20%24%20signs)%20then%20in%20your%20case%20change%20the%20%22Applies%20to%22%20section%20to%20the%20range%20%3D%24A%242%3A%24A%2450%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806789%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392269%22%20target%3D%22_blank%22%3E%40melissach%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20in%20some%20cell%20of%20the%20each%20sheet%20the%20name%20of%20the%20previous%20sheet%2C%20let%20say%20in%20Feb!C1%20it'll%20be%20%22Jan%22%2C%20in%20Mar!C1%20-%20%22Feb%22%2C%20etc.%3C%2FP%3E%0A%3CP%3EAfter%20that%20you%20may%20apply%20conditional%20formatting%20rule%20in%20Feb%20sheet%20with%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DA1%26gt%3BINDIRECT(%24C%241%26amp%3B%22!%22%26amp%3BADDRESS(ROW()%2C1%2C4))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(I%20started%20range%20from%20A1%2C%20otherwise%20adjust%20ROW()%20part)%3C%2FP%3E%0A%3CP%3EBy%20Format%20Painter%20you%20may%20copy%20this%20formatting%20on%20next%20sheets%20(Mar%2C%20Apr%2C%20etc)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806950%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806950%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%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20so%20I%20think%20there%20is%20something%20I'm%20not%20understanding%20or%20it's%20just%20not%20working%20for%20me...%20Here%20is%20my%20worksheet...%20maybe%20that%20would%20help%3F%20Thanks%20again%20for%20your%20help%20guys!%20I%20really%20appreciate%20it!%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%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20need%20to%20know%20if%20my%20score%20has%20increased%20or%20decreased%20since%20the%20previous%20month.%20All%20months%20have%20the%20same%20set-up...%20I've%20also%20attached%20it.%3C%2FP%3E%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%2F127171i66E2DEE37BD302A1%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-806982%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392269%22%20target%3D%22_blank%22%3E%40melissach%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%20I%20created%20rule%20for%20Feb%2C%20added%20March%20sheet%20and%20apply%20same%20formatting%20to%20it%20by%20Format%20Painter.%20Sheet%20name%20is%20in%20F1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806994%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806994%22%20slang%3D%22en-US%22%3EWow%20Sergei%2C%20Thank%20you%20soooo%20much%20for%20your%20help!%20I've%20wasted%20so%20much%20time%20trying%20to%20figure%20this%20out!%20You're%20a%20life%20saver%20and%20I'll%20mention%20that%20you%20helped%20me%20to%20my%20boss%2C%20haha!%20Have%20a%20great%20day!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807000%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392269%22%20target%3D%22_blank%22%3E%40melissach%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%2C%20have%20a%20nice%20day%20too!%3C%2FP%3E%3C%2FLINGO-BODY%3E
melissach
New Contributor

Hello,

I have multiple worksheets in Excel 365 that present data from one month to another. I am trying to make the data turn green if it's gone up since the previous month or red if it's gone down. I would like to know the quickest way to copy-paste the conditional formatting so it is relative to the previous month and not reference the original cell in the formula. ex: I have sales in column A2 to A50 in worksheet february and would like to format it n relation to column A2 to A50 in worksheet january while matching A2 feb with A2 jan, A3 feb with A3 jan... I hope I'm being clear. If it's easier with Icons, I can do that instead. Thanks!

8 Replies

For February, you could use conditional formats with these formulas:
(RED)
=A2<January!A2
(GREEN)
=A2>January!A2

Make sure to apply the conditional formats to your necessary ranges.

 

Then for each month that follows just simply change "January" to the previous month.

Hi @PReagan 

 

That works but how do I copy paste it to the rest of my cells without it comparing to A2 but to the following cells automatically. If I want to compare A2 (january) with A2 (february), A3 (january) with A3 (february) and so on?

 

Thanks!

Make sure that the conditional format formula uses relative references (i.e. no $ signs) then in your case change the "Applies to" section to the range =$A$2:$A$50

@melissach 

You may add in some cell of the each sheet the name of the previous sheet, let say in Feb!C1 it'll be "Jan", in Mar!C1 - "Feb", etc.

After that you may apply conditional formatting rule in Feb sheet with the formula

=A1>INDIRECT($C$1&"!"&ADDRESS(ROW(),1,4))

(I started range from A1, otherwise adjust ROW() part)

By Format Painter you may copy this formatting on next sheets (Mar, Apr, etc)

 

@Sergei Baklan 

 

Ok, so I think there is something I'm not understanding or it's just not working for me... Here is my worksheet... maybe that would help? Thanks again for your help guys! I really appreciate it!

 

So, I need to know if my score has increased or decreased since the previous month. All months have the same set-up... I've also attached it.

clipboard_image_0.png

@melissach 

Please check attached. I created rule for Feb, added March sheet and apply same formatting to it by Format Painter. Sheet name is in F1.

Wow Sergei, Thank you soooo much for your help! I've wasted so much time trying to figure this out! You're a life saver and I'll mention that you helped me to my boss, haha! Have a great day!

@melissach , glad to help, have a nice day too!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies