Highlighting the difference between actual and target

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:

Original data table

Add three extra columns in between your actual and target columns and label them “Copy”, “Diff mid” and “Difference”:

new columnsIn the “Copy” column simply copy the data from the actual column.

In the Diff mid column, in cell D2, type in the following calculation:


In the “Difference” column, in cell E2, type in the following calculation:


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

insert line chartA chart like the one below should appear:

line chartDelete 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:

high-low lines

Format the lines as you wish by clicking on them and pressing Ctrl+1.

format high-low lines

I changed the width, made them dotted and changed the colour to match the target line.  My chart now looks like this.

formatted high-low lines

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.

format data series

Your chart should now look like this:

secondary axis

Adjust your secondary vertical axis so that the maximum is the same as your primary vertical axis.

adjusted secondary 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 series chart type

Change it from a basic line chart to a stacked line chart and press the OK button.

change chart type

Your chart should now look like this:

stacked line chart

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.

data labels

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”

select data source

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.

format data labelsYou 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:

cool chart

Hope it helps!

+Alesandra Blakeston

Related posts:

Conditional formatting intersect area of line charts

area filled intersect


2 thoughts on “Highlighting the difference between actual and target

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Connecting to %s