Sometimes it is important to know the performance against a target. There are of course lots of ways of doing this, my favourite being bullet charts. That being said, you can also show this with line charts if you have a lot of data points over time for example.
If you are a regular reader you will have seen an example of this chart last week in this post. I promised I would show the “how to” and here it is. You can download the sample worksheet here.
Step 1: Set up your data table.
Your original data table with the actual and target columns will probably look something similar to the one below:
Add three extra columns in between your actual and target columns and label them “Copy”, “Diff mid” and “Difference”:
In the “Copy” column simply copy the data from the actual column.
In the Diff mid column, in cell D2, type in the following calculation:
=E2/2
In the “Difference” column, in cell E2, type in the following calculation:
=(F2-B2)
Copy and paste the calculations to the last row in the table.
Step 2: Create the line chart
Highlight the whole table and click on Insert > Chart > Line > 2-D Line > Line
A chart like the one below should appear:
Delete the gridlines and legend.
Step 3: Format the chart
Add the drop lines
Click on the “Target” line (the top most line in the chart), then click on chart tools > Layout > Lines > High-low lines. Your chart should update as shown below:
Format the lines as you wish by clicking on them and pressing Ctrl+1.
I changed the width, made them dotted and changed the colour to match the target line. My chart now looks like this.
Format the remaining lines
Delete the “Difference” series. You aren’t going to need it as a series.
Then click on the “Copy” series and press Ctrl+1. Move it to the secondary axis. Repeat for the “Diff mid” series.
Your chart should now look like this:
Adjust your secondary vertical axis so that the maximum is the same as your primary vertical axis.
You should be able to delete the secondary axis now.
Adjust the series on the secondary axis
You’re almost there. Right click on the “Diff mid” series and select Change series chart type.
Change it from a basic line chart to a stacked line chart and press the OK button.
Your chart should now look like this:
Click on the “Copy” series (red line) and press Ctrl+1. Change it so that it has no line (it’s a duplicate of the blue line which is currently hidden).
Before closing the formatting box, click on the “Diff mid” series and also give it no line. Then press the Close button. With the series still selected, click on Chart tools > Layout > Data labels > Center. Your chart should update as follows.
Update data labels
Unfortunately, these aren’t the labels we want. The labels we want are in the “Difference column”. So click on the chart and choose “Select data”
Because the “Diff mid” series is plotted on the secondary axis, it can have different horizontal axis labels to the “Actual” series and the remainder of the graph. So click on the “Diff mid” series and then click on the Edit button for the horizontal (Category) Axis Labels.
Change the reference to the “Difference” column:
='Target vs actual'!$E$2:$E$9
Then press the OK button twice to close the menu box.
Click on the data labels and press Ctrl+1 once more.
In the pop up menu box that appears, deselect the Value checkbox and check the Category name checkbox. Then press the close button.
You can also format the data labels as you wish (change the colour, the fill, font size etc.) Your finished chart should look something like this:
Hope it helps!
+Alesandra Blakeston
Related posts:
Conditional formatting intersect area of line charts
User Friendly 