Forum Discussion

MegGarcia's avatar
MegGarcia
Copper Contributor
Sep 24, 2024

Conditional formatting using icon sets and relative references

Would appreciate some help, please!

 

Column F contains current scores 1-20

Column G contains previous scores, also 1-20

 

I need column G to highlight whether the scores have gone up, down or stayed the same.

There’s already conditional formatting in column G (red fill for values > 12 , amber fill for 8-12 and green for <8)

 

I tried to use conditional formatting (format all cells based on their values) with icon sets and a formula, but excel doesn’t allow relative references for icon sets and I do need excel to compare cells within the same row, ie G2 with F2, G3 with F3, etc (ideally ignoring blank cells).

 

Is there any other way to do it?

  • MegGarcia You can create a helper column to do this.  Add a formula to a new column and just use something like =G2-F2 (then fill down).  Add your conditional formatting to the new column, and set up your rule as shown in the screen shot below.  Use the checkbox for "show icon only" if you don't want to see the value.

     

     

    • MegGarcia's avatar
      MegGarcia
      Copper Contributor
      I was trying to avoid adding yet another column as the spreadsheet is already quite wide, but looks like I'm going to have to do it anyway. Thanks for your help.

Resources