# Conditional formatting for heat map

##### New Member
I am trying to set up a heatmap in excel 2007 using conditional formatting. I need all ABS(2.5) and above to be red, all values between ABS(2.4) and ABS(.99) to be yellow and all values less than ABS(1) to be green. I cannot figure out the right equation for my middle criteria (yellow) and also I cannot get excel to recognize the negative numbers. For example it keeps showing all the negative numbers as green.

Thank you,

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
so I'm a little confused on your 'green'. If you want everything less then 1 green it would conflict with the lower end of 'yellow'.
so maybe everything less than .99 ?

you should select the range with the values before entering the conditional formatting formulas, just kinda makes it easier

Red =ABS(A2)>=2.5

yellow =AND(ABS(A2)<2.5,ABS(A2)>0.99)

Green =ABS(A2)<.99

so I'm a little confused on your 'green'. If you want everything less then 1 green it would conflict with the lower end of 'yellow'.
so maybe everything less than .99 ?

you should select the range with the values before entering the conditional formatting formulas, just kinda makes it easier

Red =ABS(A2)>=2.5

yellow =AND(ABS(A2)<2.5,ABS(A2)>0.99)

Green =ABS(A2)<.99

Hi Weazel - Yes I mistyped the green parameter. It should be .99. I typed in what I thought was your forumulas exactly but it is still not working. These were the sample numbers I used. ALl the positive numbers turned green and all the negative numbers turned red.
 March June Sept Dec Dow 0.99 3.6 1.4 2.1 Vix -0.7 -2.5 -6.2 4

<COLGROUP><COL style="WIDTH: 54pt" span=5 width=72><TBODY>
</TBODY>

ok,

maybe I'm just confused, but maybe try it like this.

where B4 is green, B5 is yellow, and B6 is Red

Excel 2012
ABCDE
1MarchJuneSeptDec
2Dow0.993.61.42.1
3Vix-0.7-2.5-6.24
4FALSE
5TRUE
6FALSE

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
B4=ABS(B2)<0.99
B5=AND(ABS(B2)>=0.99,ABS(B2)<2.5)
B6=ABS(B2)>=2.5

<tbody>
</tbody>

<tbody>
</tbody>

ok,

maybe I'm just confused, but maybe try it like this.

where B4 is green, B5 is yellow, and B6 is Red

Excel 2012
A
B
C
D
E
1
March
June
Sept
Dec
2
Dow
0.99
3.6
1.4
2.1
3
Vix
-0.7
-2.5
-6.2
4
4
FALSE
5
TRUE
6
FALSE

<TBODY>
</TBODY>
Sheet2

Worksheet Formulas
Cell
Formula
B4
=ABS(B2)<0.99
B5
=AND(ABS(B2)>=0.99,ABS(B2)<2.5)
B6
=ABS(B2)>=2.5

<TBODY>
</TBODY>

<TBODY>
</TBODY>

When I do this it only works on the cell that is in the forumulas. What am I doing wrong?

you would need to select b2:e3 first, then apply the formatting

you would need to select b2:e3 first, then apply the formatting

when i select b2:e3 like so, ABS(b2:e3), it returns #value! to the B4 -B6 cells and the formatting doesn't work at all.

you would need to select b2:b3 first.

after its selected enter the formulas in conditional formatting just the way they are

you would need to select b2:b3 first.

after its selected enter the formulas in conditional formatting just the way they are

Still doesn't work. Here's what I'm doing:

 A B C D E mar Jun Sep Dec DOw .99 3.6 1.4 2.1 Vix -.07 -.25 -6.2 4 True '=ABS(B2)<0.99 False '=AND(ABS(B2)>=0.99,ABS(B2)<2.5) False '=ABS(B2)>=2.5

<TBODY>
</TBODY>

I entered the above information in excel - the B rows 5-7 hold the equations that I typed in column C. I select B3:E4. I then click on conditional formatting and select New Rule. I picked "format all cells based on their values". And I changed format stype to 3-color scale. In the minium, midpoint and max boxes I change the type to "formulas" I then put = B5 in the value for min, =B6 in the value for mid, and =B7 in the value for max. When I do this B3:E:3 turn green, along with E:4 and C4:D4 turn red.

What in the world am I doing wrong?!?

Ok, I think I understand...instead of using 3 color scale go to conditional formatting ... new rule....use a formula to determine which cells to format....then in the format values where this formula is true paste in the first formula click on format and select the color for the first rule.

you would repeat this process for the 3 rules, selecting your desired color.

remember to select the range prior to creating the conditional format if it doesn't stay selectled

Replies
16
Views
286
Replies
2
Views
204
Replies
3
Views
161
Replies
1
Views
236
Replies
3
Views
198

1,219,770
Messages
6,150,166
Members
450,937
Latest member
kattyg261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back