Forum Discussion

melissach's avatar
melissach
Copper Contributor
Aug 14, 2019

Relative conditional formatting

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!

excel
Formatting
Formulas and Functions

8 Replies

Sort By
  • PReagan's avatar
    PReagan
    Bronze Contributor
    Aug 15, 2019

    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.

    • melissach's avatar
      melissach
      Copper Contributor
      Aug 15, 2019

      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! 🙂

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 15, 2019

        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)

         

Resources