Struggling to Achieve the Conditional Formatting Heat Map I want

[slam]

Hi everyone!

I'm trying to set this up:

• -10,000% to -100% solid red
• -99% to -1% decreasing red gradient
• 0% white
• 1% to 99% increasing red gradient
• 100% to 10,000% solid red

I'm struggling to find the right settings in Conditional formatting to achieve it. Don't know if I should be using number, percent, percentile, etc, or what to set the values to. How should I set this up?

Thank you so much!

 

[DSCfromCFA]

G'day Slam,

It appears that CF won't allow negative percentages, so you may need to work with numbers, (which will mean you can't use the % symbol, but if your put that in the column header then it is not absolutely needed). It is quite simple in that case, simply select Colour Scales under the CF icon choose a three colour option and and set your minimum value as -100, mid value as 0, and max values as 100, then select solid red for the min and max and white for the mid. You don't need the extreme percentages as the solid red will be applied to any value outside the limits specified.

Aside from the percentage issue there is another potential problem, black text in solid red is hard to read, so a light font colour is preferred - typically white, which is clearly not going to work here. This may not be a problem if the fonts size is increased.

cheers

shane