Customize Line charts for added analysis

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:

initial line chart

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:

Data table

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):

insert line chartYou’ll see a chart like the one shown below:

Attendees chart

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.

marker options formatting menu box

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:

format data point

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):

Adjusted data pointsB: Change the colour of the dots to show Positive and Negatives

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.

change desired fill colour

Repeat for any data points you want to highlight

Variably filled data pointsOnce complete, close the menu box by click on the close button.


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.

Fx text box

Reposition the label by clicking on it and moving it.  Repeat as needed.  Your graph should look like the one below:

Edited data labels

And that’s it!  Hope it helps!

+Alesandra Blakeston



3 thoughts on “Customize Line charts for added analysis

  1. Can this be done dynamically? Can the shapes be changed conditionally based off of the chart data? This is the closest one on the idea I had that I tweeted you about (since there wasn’t any other way to contact directly/privately)

    I have 5 variables (including axis) and looking at a line chart.

    Y Axis is sequential numbers 1-X (Project numbers)
    X Axis is Months 1-12

    Other variables:

    1) Category from 1-7, which I want color coded
    2) Department 1-5, which I want the shape to change based off of
    3) People which is initials

    It is to plot a filterable view of our projects and milestones for the year. I’d like to be able to filter by department and person.

    If this is even possible it’s something I’m willing to pay for, although I expect you would need more info – if you think it is possible send me an email and I’ll further explain.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s