How do I define a custom color scale in conditional formatting?

Question

I have a table of number values (Positive and Negative) with which I have applied a conditional formatting color scale which highlights the lowest number as dark red and the highest number as dark green

This tends to highlight some of the positive numbers in red but in the context of my sheet any number greater than 0 is considered good and as such should be green.

Is there a way I can apply a color scale to numbers <= 0 with a scale of light red for 0 down to dark red for the lowest number and for positive numbers, the lowest positive number should be light green and the highest positive number dark green with numbers in between varying in shade based on value?

Also see my answer here: http://stackoverflow.com/questions/33925935/conditional-formatting-color-gradient-with-hard-stops/33926498#33926498 — Brett Wolfington, Nov 25 '15 at 21:15

score 2 · Answer 1 · answered Mar 18 '15 at 16:24

2

You only need to edit your color scale rule.

Here is a screenshot to help out.

The key is to make sure you have a 3-Color Scale and set the Midpoint to the Number type and a value of 0.

enter image description here

answered Mar 18 '15 at 16:24

guitarthrower

5,624
3
29
37

score 1 · Answer 2 · answered Mar 18 '15 at 17:01

I agree with 3-Color Scale but suggest the colour for the middle be white (as you say only want red and green) and that Percentile be applied for both Minimum and Maximum to avoid the possibility that a single high positive number may 'steal' all the green colour. What you consider the appropriate value for your Percentiles may need some adjustment to suit your data.

How do I define a custom color scale in conditional formatting?

2 Answers2