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.
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.
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
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.
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.
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.
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