Query on Excel Chart Wizard

[Keerthivasan]

Hi,

I have the below data. Was jus wondering if there is a way in the chart/bar graph to include a conditional formatting.


<TABLE style="WIDTH: 367pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=488 x:str><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: green; WIDTH: 48pt; HEIGHT: 18pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=24 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: green; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=86>Production</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: green; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=65>QC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: green; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=95># Errors</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: green; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=73>QC%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: green; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=105>Accuracy %</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>702</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>240</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl28 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl29 x:num="0.34188034188034189">34.19%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; COLOR: green; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-background-source: auto; mso-pattern: auto" class=xl30 x:num="0.98750000000000004">98.75%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>702</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>240</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl28 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl29 x:num="0.34188034188034189">34.19%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; COLOR: green; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-background-source: auto; mso-pattern: auto" class=xl30 x:num="0.98750000000000004">98.75%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>769</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>194</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl28 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl29 x:num="0.25227568270481143">25.23%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; COLOR: red; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl30 x:num="0.97422680412371132">97.42%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>874</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>279</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl28 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl29 x:num="0.31922196796338675">31.92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; COLOR: green; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-background-source: auto; mso-pattern: auto" class=xl30 x:num="0.98566308243727596">98.57%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>750</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>209</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl28 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl29 x:num="0.27866666666666667">27.87%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; COLOR: green; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-background-source: auto; mso-pattern: auto" class=xl30 x:num="0.9856459330143541">98.56%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>77</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>44</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl28 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffcc99; COLOR: windowtext; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl29 x:num="0.5714285714285714">57.14%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffff99; COLOR: #ff6600; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" class=xl30 x:num="0.97727272727272729">97.73%</TD></TR></TBODY></TABLE>

Below is the threshold

Red- <97%.5
Amber- 97.5% - 98.5%
Green- >98.5%

X-axis would be name and Y-axis would be the accuracy percentage. Is it possible to have each bar in Y-axis showing different colors automatically according the threshold

Thanks
Keerthi

 

[VoG]

Take a look at Conditional Charts http://peltiertech.com/Excel/Charts/ChartIndex.html

 

[Keerthivasan]

That was really helpful... Thank you much

 

[Jon Peltier]

Coincidentally I've rewritten the tutorial this week, Conditional Formatting of Excel Charts.