Conditional Formatting - Positive/Negative Values

Hi

I've made a report which has performance vs target for various team members, as a %. I want to conditionally format this column so that negative values are red, with lower values being increasingly stronger shades of red, and so that positive values are green, with higher values being increasingly stronger shades of green.

Is this even possible? If so how can it be done?

Any help would be appreciated.

Thanks

A basic solution would be to create a simple set of rules. Depending on your criteria, the number of rules can change. Set these rules up in the 1st cell, then use Format Painter to apply the rules to any other cells needed.

Here is an example:
1. =A1>0, cell color light green
2. =A1>25, cell color green
3. =A1<0, cell color light red
4. =A1<-25, cell color red
etc.

Or you can do the following:

conditional formatting -> color scales -> more rules -> 3-color scale -> set midpoint number to 0

That sounds better for sure, try it

Thanks guys, I'll give it a try now

Originally Posted by JazzKult

Or you can do the following:

conditional formatting -> color scales -> more rules -> 3-color scale -> set midpoint number to 0

I stumbled across this post looking for exactly this issue. The solution proposed here is what I tried before googling; it does not work if the highest negative or highest positive is very different in absolute terms. I think it needs a formula in the rule, which I will now try to figure out.