How to Color data bars in excel based on conditional formatting

Question

I have the following column in a sheet

I would like to plot the data bars in the Variance Column and 

also format them conditionally 

My requirements are :

1. Min value =0 max value=100
2. Bar will automatically stretch in cell based on cell
3. Bar will get a green color if percentage=0 
4. Bar will get a blue color if percentage >0 but <10
5. Bar will turn red if the percentage >10 

I want the bars to be shaded with the colors not the entire cell , only the color change should reflect in bar 

and not in the entire cell

Any suggestions 

I tried using conditional formatting but unable to get the color only in bars inspite i end up coloring the entire cell.

Answer 1

You get one or the other - not both. If you have data bars, the length shows the relative value, not the color. If you want colors, then you don't get bars.

Answer 2

Hi,

If the following is close to what you expect have a look to TarunVariancePlot.xlsx here for the formulas and Conditional Formatting rules:

As you can see the Variance actually takes 10 columns.

Hope this helps

Cheers

Lz.

Answer 3

You can also use this technique for "Conditionally Formatted In-Cell Charts" using just one cell per row:

http://peltiertech.com/Excel/Charts/InCellFormulaCharts.html

Answer 4

Hi,

Did any of the above proposals help? Your feedback (and Answer if applicable) would be appreciated. Thanks in advance

Cheers

Lz.

Answer 5

Hi 

Thanks for your help , but this isnt the pattern i am looking for.

The bars in Range C5 , C6 and C8 have the identical bar length although they

hold different percent values 

Can we have a fix on this.