Need help getting column charts to change colour based on values

Hi all.

Working on a dashboard chart for work and I am having trouble getting the colours in the charts to update automatically based on the source datas value (excel 2003). I've read around the net and can see sites that say this is possable but I'm really struggling and would appreciate some pointers.

Thanks

I would need more detail on what you're trying to do to provide a proper solution.
Please see attached as an example and let me know if this is what you're looking for (It's a column chart with 2 different colors. Green for positive numbers and Red for negative numbers).

If you have any questions or would like an explanation on how it works please let me know.

ChartColorExample.xlsx

I've attached the spreadsheet I'm working on. The main data is on the 'Daily' tab, with columns AJ & AK showing the data I want to make my charts from. I need to enter a way of the bar charts changing from Red, Amber and green depending on what value is in the source data. For example, for incoming telephone calls I would want the ranges 0-199 to show green, 200-399 to be amber and 400-700 to show red in the bar chart. I also want to have calls abandoned to be on the same chart but using a different range (if possable of 0-4 (G)5-9 (A) and 10-30 (R).

You're example sheet was close to what I need but how do I add a 3rd series?

Thanks for the help

This should do the trick. Note that I have not linked the graph to your data, but I'm sure you can figure out how to do that.

Basically, you need to make a series for each color and you need another series for the data labels. The color series should equal zero when it doesn't show the color and the value you want when it is in the color range. You then graph all of these series and set the overlap to 100% and format the colors. Then, add the actual values to the graph but set it to no fill and have it show data labels.

Let me know if this is similar to what you want and if you have any questions on how it works.

Stats - Color Changing Graph.xls

MitchC - I cannot thank you enough. This has been a massive help. One (hopefully!) last thing. I also need the charts to show %ages. What do I need to do to the formula/chart for it to pick up a %age instead of a number? I've attached my updated spreadsheet to show what I mean on the chart data tab.

Thank you again.

I'm not really sure what you mean and I don't see a new spreadsheet attached.

If what you're asking about is axis size, you can format it to make the maximum 1 and the minimum 0. Then all percentages will fall in that range and should appear on the graph.

If this isn't what you're referring to, please provide an updated spreadsheet with an example or more clarity on what you need.

Sorry, here's the attachment.

If you look on the chart data tab, I need to apply the same kind of formula to the percentage values (eg, green 100%-90%, Yellow 89%-85%, Red under 85%), source data in cells B9-B11.

Here you go:

CS Stats WIP - Color Chart.xls

The methodology is the same. You just need to change the conditions on the values that represent the color series.
Hope this helps. And good luck with the Dashboard!