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?

Answer 1

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.