Column charts - format colour by value

[Bernster]

Is there a way of formatting a column by the value - eg green for >= 98%, red
for <98%?

 

[Bernard Liengme]

Jon Peltier tell all at
http://peltiertech.com/Excel/Charts/ConditionalChart1.html
best wishes

 

[ShaneDevenshire]

Hi,

There are two possible questions here -
1. Can you format the entire column green if it goes above 98%
2. Can you format the part of the bar that goes above 98% green while
leaving the remainder red.

For the first one -
Suppose the series in question is in cell B2:B100
Then in cells C2:C100 enter the formula
=IF(B2>=98,B2,0) and copy it down
In the cells D2100 enter the formula
=IF(B2<98,B2,0) and copy it down
Highlight these new ranges and the x-axis label range, but exclude the
B2:B100 range, and chart your data.
Format one series to Green and one to Red.

For the second -
You can use a stacked column chart for this one
Using the same range as above enter one formula:
=IF(B2>98,B2-98,0)
and the other
=IF(B2<=98,B2,98)
to plot the data in C and the labels but make the chart a stacked column.
You may need to switch the series so that the correct one is on top. In
2003 double-click the series and select the Series Order tab, make the
necessary adjustments.