How to add additional information to the Excel graph

My data:

• time of the day, this will be used for x axis,
• working order,
• worker,
• number of good parts,
• number of bad parts.

It looks like this:

My question - how can I add the WorkingOrder and Worker to this graph so that the user would know from the graph that first two records are for WorkingOrder 123 and the rest for WorkingOrder 124 (similar for Worker)? I imagine there should be one horizontal line or timeline with caption and one for Worker, but have no idea how to to this.

So... something like that:

It could also be another type of graph, the additional data could be represented with colors for example or anything else, as long as the graph somehow shows all the data needed.

2 Answers

You can accomplish something fairly close to your example without too much trouble, but you'll need Rob Bovey's awesome add-in Chart Labeler. Starting with your chart and data:

1. Convert your data to an Excel table.
2. Add two helper columns, Worker_label and WO_label
3. Add the following formulas for your two new columns:
   Worker_label =-2
WO_label =-4
4. Add these two new data series to your chart.
5. Create a second table to use for a modified Y axis, with 3 columns X, Y, Label and 19 rows.
   X fill with all 0's
   Y fill from -6 thru 12
   Labels leave -6 thru -1 blank and 0-12 = 0-12.
6. Add the data from your second table as a fifth data series to your chart-it will replace the default Y-axis.
7. Format the vertical axis.
   Minimum = -6 Maximum = 12 Major unit = 1 Major tick = none Axis Labels = none
8. Add default labels to your Worker, Work Order, and custom Y axis data series and format to taste (this formatting will carry-over to your custom labels in the next step).
9. Add Custom Labels to your Worker, Work Order, and custom Y axis data series, using the original data columns with text values.
10. Format everything to taste.

Create a new column F Custom Label
In F2 write =B2&"-"&C2
and drag it down
After inserting the Chart, Click on Chart Tools, Layout, Data Labels and choose where you want the labels (it will show : Good values and Bad values)
Select each value alone (Left click on 10 once all will be selected, Left click on it a second time it will select this one only) Now click in the formula bar after fx where you see the content of the cell and write = and click on the value you want in the column of Custom Label it will look like =Sheet2!$F$2
Now press Enter
continue the same for all the Data Labels