Color cells by absolute value in a range in Excel 2010

I don't think that there is any way to do this with three colors (red, yellow, green), but you can do it with two colors (for example yellow and green). Simply make the color for the low value and the color for the high value the same. That way, the cells with the lower absolute value will have the middle color and cells with the higher absolute value will have the other color.

• Select Your data
• Conditional Formatting
• Color Scale
• More Rules
• Select "3-Point Scale" under Format Style
• Change the colors so that the Maximum and Minimum colors are the same

Here is my solution to this problem. The conditional format formula reads

=AND(ABS(B3)>0,ABS(B3)<=500) 

for the darkest green, the scale changes to 500 to 1000, 1000 to 1500, and finally 1500 to 2000 for the red band.

Conditional Formats

Color Scale Values

Here is a picture of the dataset that I used to test these conditional formats:

A variation on this simple conditional formatting illustration may work for you.

Highlight the whole of the data range (you need the top LH cell to be the anchor for relative addressing) and enter the Formula: in 'relative notation' i.e. cell references without the dollar signs. You also have to consider the order of the rules.

The uppermost formula is obscured but reads =(ABS(B3)>39) * (ABS(B3)<41) Note that the * symbol applies an AND operation.