Create an Excel Line Chart with Target Range

With an Excel line chart, you can show the sales results from a date range, to see how things have gone. For example, in the chart shown below, you can see the sales quantities for the first six months of the year.

chartlinetargetrange02

Add a Target Range

To give a better picture, you might also want to show what the sales targets were for each month of the year.

In the next chart, a blue band with the target range has been added, and you can quickly see which months were over or under the target amount.

chartlinetargetrange

Set up the Data

The sales data for the chart is in columns A:B in the worksheet shown below. The high value for the monthly target is entered in column C for each month, and the low value in column D.

Then, in column E, a formula calculates the difference between the high and low:

= C4 – D4

chartlinetargetrange03

Create the Chart With Target Range

Once the data is set up, select all the data and headings, except for the Target High column.

Create a stacked column chart from all the data, and change the Sales amounts to a Line chart type.

chartlinetargetrange05

For the two Target series, hide the series that is the low value, and leave just the “Difference” amount – the range between the low and high values.

chartlinetargetrange04

With a little formatting, you can change the column chart’s appearance so the target range looks like a solid block, instead of individual columns.

Change the gap width to 0%, set the border to no line, and change the colour if you’d like something other than grey.

chartlinetargetrange06

Video: Create a Line Chart with Target Range

Watch this short video to see the steps for making an Excel line chart with target range.

Or watch on YouTube: Create an Excel Line Chart With Target Range

Download the Sample File

To see how the chart works, you can download the sample file from my Contextures website. On the Excel Sample Files page, go to the Charts and Graphics section, and look for CH0008 – Show Target Range on Line Chart

____________________________________

You may also like...

2 Responses

  1. Andrew Mundy says:

    A simple and clever idea which is effective and easy to implement. What is the purpose of the T_High column in the data table? It is not needed for the chart to work but does make understanding the table easier. Is that the reason the column is in this example?

    Regards

    Andrew

  2. Andrew Mundy says:

    After watching the video the columns make sense. The ‘Target’ column is a “helper” column to get the chart to work while the ‘T_High’ and ‘T_Low’ columns contain the input data.

    Moral of the story; watch video before asking questions!

    Regards

    Andrew

Leave a Reply to Andrew Mundy Cancel reply

Your email address will not be published. Required fields are marked *