Excel 2003 color coded column chart

[lespoils]

Hi, i have to do this chart and not sure how to go about it.

First, I want to compare different work shift's productivity for a week and show it in an column chart. Lets say on moday shift B was day and shift D was night shift. shift B did 6000 tons, shift D did 3000 tons. so for monday, shift B and shift D are shown as two columns.

My problem is that, i want to color of the column to show if an accident happened. Red if yes, green is nothing happened.

Is there anything like conditionnal formating that could do this ? or do i have to go VBA ?

Another question, i added a line chart for budget tons to the column chart. i would like to send it to back, can't do it. the line now passes over the columns, i would like it to pass behind. I did try to change serie's order, but when i select the line the only existing serie is the budget itself.

hope i'm beeing clear.

Lespoils

 

[Gerald Higgins]

Hi, welcome to the board.

Making chart colours depend on some condition is easy, you just need to set up your data correctly in the first place.

Something like this....
Col A......Col B......Col C......Col D......Col E..............Col F.....................and so on
B prod....D prod....B Acc ?...D Acc ?...B Prod No Acc..B Prod Acc.............and so on
3000......6000......NO..........NO.........3000..............0..........................and so on
4000......5500......YES.........NO.........0..................4000......................and so on

Use a formula like this to populate Col E

=if(C2="YES",0,A2)

and similarly for Col F.

Then, instead of using Col A as the source data for your chart for shift B, use 2 chart series for shift B, 1 based on Col E and 1 on Col F.
Make one series red and the other green, or whatever.
Do the same for shift D.

 

[lespoils]

Thanks, it works.

But i need to show data labels on chart columns because i need to know what shift did the tonnage. If i simply add labels, it shows labels for all the 0 values. Also since there is now 2 series per shift, it shows a lot of unwanted labels.

Any idea how i can show labels only when there is a value different of 0 ?

thanks

 

[Gerald Higgins]

You can do this by adding another chart series, just to position the labels.
Make it a line chart, set the labels to appear, and then set the colour and symbols for the line to none, so that the line itself does not appear, just the labels floating where you want them to be.
Again, this depends on you setting up your data correctly for this additional series.