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

Hi,

I knew this. I just provided a sample to demonstrate how this could work. Of course this is "fixable"...with 100 columns for the Variance "column". Is this what you want (not sure this makes sense)?

Keep in mind it won't be possible to merge cells and as you want the %age to be displayed in the appropriate column its width will need to be large enough to display the number.

An alternative would to be to create 11 columns for 0 thru 10% and 9 other columns (a bit larger to keep some proportionality) for 11% thru 20%, 21% thru 30%...

With the above guidance and the sample I uploaded earlier it's probably something you should implement directly on your real life workbook as this requires time... Have a look to sheet2 in TarunVariancePlot.xlsx here. I implemented the formulas, not the Conditional Formatting rules (cells colored manually).

Hope this helps

Cheers

Lz.

Answer 2

Least innovative response ever.

Answer 3

Excel is just behind the times. Soon enough this feature will be implemented, just like all the others we 'work-around' for years prior. Nothing new here