Heatmap Formatting

[yankeez245]

Hello,

I am trying to make a heatmap but Excel is only showing extreme values of the color scale. I have attached a screenshot.

Why is the 69% yellow but the 45% is red? This is on a scale of 0%-100%, so all numbers between 30% and 70% should be a shade of yellow, while all colors less than 30% should be a shade of red and greater than 70% should be a shade of green.

How can I make the heatmap coloring more fluid between yellow, green, and red?

 

[Eric W]

Because if you just selected the 3-color option, Excel will default to using the low value and high value as the ranges, in this case 41% and 92%. That is a range of 51%, 1/3 of 51% is 17%, so red is from 41% to 58%, yellow is 58% to 75%, green is 75% to 92%. If you want to go from 0 to 100%, you need to change the parameters. After you enter the Conditional formatting, click Conditional Formatting > Manage Rules > select your rule > Edit rule > and change it like this:

Here is how it will change:

Book5
	E	F	G	H	I	J
2	Brazil	Chile	Colombia	Mexico	Peru	Spain
3	45%	92%	69%	81%	41%	88%
4
5	Brazil	Chile	Colombia	Mexico	Peru	Spain
6	45%	92%	69%	81%	41%	88%
Sheet19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:J6	Other Type	Color scale		NO
E3:J3	Other Type	Color scale		NO

 

[yankeez245]

Wow incredibly helpful, and that worked.

Thanks very much for your help!

 

[Eric W]

Happy to help!