4 Replies Latest reply on Dec 12, 2016 8:35 PM by Mahfooj Khan

# Conditional Formatting of color based on a numerical value?

I want to use the "packed bubbles" chart as a heatmap.  I have an excel worksheet that creates a numerical score for a series of data based on a weighted average scoring system.  The result is a numerical score between 0-10.  I want the bubbles on the chart to be red for items that score 7-10, yellow for 4-6, green for 1-3 and grey for 0.  How would I be able to do that?  So a basic example of the data would be:

 vdisk_name capacity Total ITGC2ESXPROD_1_500GB 500 9 itgcurs210_1342_512Gb 512 9 ITGC2ESXPROD_24_1024GB 1024 7 itgcurs210_1343_512Gb 512 9 itgcurs278_1199_50GB 50 2 itgcurs157_1035_40GB 40 4 itgcurs321_1636_70GB 70 2 itgcurs59_156_100GB 100 2 itgcursj0512_967_50GB 50 2 itgcurs150_1497_15GB 15 4 itgcurs183_1519_15GB 15 2 itgcurs211_778_275GB 275 4 itgcurs310_1171_50GB 50 2 itgcurs203_715_25GB 25 6 ITGC2TDQ1ESXC_50_1024GB 1024 0 itgcurs210_1311_10Gb 10 6 itgcursj0513_969_50GB 50 2

Any ideas?  Thank you in advance.

Justin Doster

• ###### 1. Re: Conditional Formatting of color based on a numerical value?

is this what u r looking for ? Try the calculation in below screenshot to color the chart based on values !

• ###### 2. Re: Conditional Formatting of color based on a numerical value?

Hi Justin,

Have a look my approach. Is that what you're looking for? Let me know If you've any query.

I've used your capacity measure on size to show your 0 items.

workbook (version 9.3) attached for your reference.

Mahfooj

• ###### 3. Re: Conditional Formatting of color based on a numerical value?

This is perfect.  How do you assign colors to the various levels.  This worked for me but I now need to figure out how to assign colors to the various levels.  I can see in your screenshot, you were able to do that.

Very Respectfully,

Justin Doster

Lighthouse Computer Services

941-400-3861

jdoster@lighthousecs.com

• ###### 4. Re: Conditional Formatting of color based on a numerical value?

If you're able to see the color legends in your worksheet then you can edit the colors from there. Or go to analysis menu tab >legends>color legend

Then select one by one data item and assign the color manually or you can simply click on assign palette.

Hope this help.

Mahfooj