Conditional Formating

[bethi]

Hello - I am looking to conditionally format, in a pivot table, a cell based on whether it is more or less than the month before. Sounds simple enough, right? Wrong (at least for me)!


My dataset lists the item in rows, the months in columns, and the sum of quantity in the values.


I want to say, "if the qty of item xyz for February is greater than the qty of xyz for January, color that green." On the reverse side, I want to say "if the qty of item xyz for February is less than the qty of xyz for January, color that red."

[TABLE="class: cms_table, width: 200"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]JAN-17 QTY[/TD]
[TD]FEB 17 QTY[/TD]
[TD]MAR 17 QTY[/TD]
[TD]APR 17 QTY[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]24[/TD]
[TD]74[/TD]
[TD]35[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD]60[/TD]
[TD]88[/TD]
[TD]90[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]HIJ[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]75[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance for your assistance.

 

[Special-K99]

People alway do this.

What happens if it's equal ?
Based on your logic so far it will won't colour the cell green or yellow.

 

[Special-K99]

With ITEM in A1

Select C2:E5

=(C2 > B2)
format as green

=(C2 < B2)
format as yellow

(remove spaces from the above formulas, forum restriction forced me to enter them)

Adjust the cell references based on where you have the table, ie if ITEM is not in A1

 

[bethi]

Good point! I'm fairly new to the world of conditional formatting, I stumbled upon it by accident, so I am not certain what I would have it do is there is no change. I would welcome any suggestions though

 

[bethi]

Thank you SO much! Your instructions were very easy to follow, and I was able to get it set up in no time.

Thanks again!