Ever wanted to plot 3 characteristics of a series of data in the same line chart? So for example you want to show:
- The period of time
- The number of persons who attended a training course
- The percentage who passed the course
You could of course plot this with two lines by calculating the number or people who passed the course:
However it is also possible to do this with one line…
Step 1: Plot your initial data for the number of attendees
Set up your data table as shown below:
Highlight the data in columns A and B and click on Insert > Line > Line (the first chart available in the drop down menu box as shown below):
Format the initial graph as needed so that the no of attendees is displayed as you wish.
Step 2: Adjust the data points to show the third variable
First click on the initial data series (Attendees) and press Ctrl+1 to bring up the formatting menu box as shown below. Then click on the Marker options tab.
Ensure that the Marker Options are set to “Built-in” and change the marker to either a circle or a square as you prefer.
A: Change the size of the individual data points
Double click on the first data point on your graph. If you have closed the formatting data menu box after the previous step, open it again by pressing Ctrl+1 again (ensuring that the first data point is selected). The title of the menu box will now say Format Data Point rather than Format Data Series:
According to my data table, the % passed for the first data point, is 70%. I am therefore going to change the size of my first data point to 35 (half the size, though you can use the full size if you wish). To do this type in 35 into the Size data entry field. Then click on the next data point and change its size and so on until you have changed them all. Note: The size has to be a whole number. If you original data is an odd number e.g. 75, half would be 37.5, so round it up to 38.
Your chart should now look similar to the one below, (kind of a cross between a bubble chart and a line chart):
In my chart I want to be able to see at a glance when the pass rate of the training is below a certain percentage (e.g. 75%). To do this, click on the first data point again and with the formatting data point menu box still open, click on the Marker Fill tab. Then click on Solid fill and change the fill to your desired colour.
Repeat for any data points you want to highlight
C: Add individual data labels to selected points
You might want to explain why some points are lower than others. In this case, click on the series and then click on Layout > Data Labels > Below.
Click twice slowly on any data label that you want to delete and then press the delete button on your keyboard. Repeat as needed.
Then click on any label you wish to add custom text to and then type in your new label into the fx text box.
Reposition the label by clicking on it and moving it. Repeat as needed. Your graph should look like the one below:
And that’s it! Hope it helps!