How to create an Excel line chart for monthly sales data, then add a target range in the chart, to show if sales fall within the expected target range.
In an Excel line chart, you can show monthly sales, over a set date range. To add context for those number, include a shaded target range in the chart. That will show if sales fall within that range, for each month shown in the line chart.
To see how to set up this line chart and target range, there are written steps, and a video, in the sections below:
--Video: Line Chart With Target Range
In this video, see how to build an Excel line chart to show sales over six months, and show the target sales range in the chart's background. In the data, add the high and low values for the target range, and show that range in the background of the line chart.
To see how the chart works, you can get the sample file from the download section on this page.
There are written steps below the video
In this example, there is monthly sales data on an Excel worksheet:
There are records for the first 6 months of the year, and this data will be used to create a line chart.
To give a better picture of the sales results, you could show what the sales target range was, for each month of the year.
To add the target range data for the chart, follow these steps:
Next, follow these steps to calculate the Target range amount:
The goal is to create a line chart, with a target range in the background. To do that, we'll start with a different chart type, then change the chart settings later.
To create a chart for the monthly sales data, follow these steps:
First, select some of the worksheet data:
Next, follow these steps to create the chart:
A stacked column chart is added to the worksheet, near the monthly data.
Tip: You can move and resize the chart after inserting it.
In the stacked column chart shown above, the blue segments in the columns represent the Qty amounts.
The Qty should be shown in a line chart, so follow these steps to create that line:
The revised chart shows the Qty amounts as a line chart, and the T_Low and Target are still stacked column segments.
Next, the two remaining segments in the stacked column need to be formatted:
In the sections below, there are steps to:
In the stacked column chart, the orange segments are the T_Low amounts. Those segments set the starting point for the Target amounts, which are represented in the grey segments.
The orange segments (T_Low) can be hidden, leaving only the Target segments visible in the stacked columns.
To hide the orange segments, follow these steps:
On the worksheet, the T_Low series is no longer visible in the chart
Tip: When the T_Low series is selected, each segment will be outlined. Click away from the chart, so the T_Low series is not selected, and those segments will "disappear".
In the completed chart, the Target range (grey series) should look like a solid block, instead of six separate columns.
Follow these steps to push the grey series segments together in the chart:
On the worksheet, the Target series looks like a solid block in the chart
Tip: When the Target series is selected, each segment will be outlined. Click away from the chart, so the Target series is not selected, and you will see a "solid" block of colour.
Instead of leaving the Target Range in its default grey colour, you can use a different fill colour.
Follow these steps to format the Target range series in the chart:
Instead of leaving the default chart title, you can link the Title box to a cell on the worksheet. That way, the chart title will update automatically, if you change the contents of the linked cell.
The worksheet has a heading in cell A1, so in this example, the chart title will be linked to that cell.
To link the chart title to cell A1, follow these steps:
Now the chart shows the text from cell A1, and will change automatically, if you edit cell A1
You can also make an optional change to the chart legend, so it does not show the T_Low series.
Follow these steps:
After you delete that entry, the chart legend just shows the target range and the monthly sales quantity.
However, the T_Low series is still in the chart, but it has no fill and no border, so it isn't visible.
Here is the full transcript for the video shown above.
'--------------------------
In this video, we'll see how to build a line chart in Excel, and behind that, show a target range for the sales.
And to show you how to build that, I'll delete this chart and we'll start from scratch.
So here's the data. I've got six months listed down the side, and there's no heading on that column.
And in the next column is the quantity that was sold each month.
Then I've got the top amount for the target, and I put that in for each month and the low amount for the target range.
And again, enter that for each month.
And in this final column, we have the target difference. So that's the gap between these two amounts.
And I click on this cell, it's just a simple high minus low.
We're going to create a chart, but not include this high column.
So I'll select the first two columns, the headings and the data.
And then press the Ctrl key, so we can select another range on the sheet.
And with that Ctrl key pressed, I'll select the low number for the target, and then the difference.
Now that I've got the data selected, I'm going to the Insert tab on the Ribbon.
And in the charts, I'll click the Column chart and then click the Stacked Column.
And that creates a sample chart with a title.
And we're going to fix this up so that we have a line and then our target range.
First we'll change this quantity series to a line,
So I'll right-click on one of the series points and click change series chart type.
If you're doing this in Excel 2010, it will look a little bit different here in Excel, 2013, we're looking for the quantity right now.
Everything is a stacked column. And for this one, we're going to change it to a line chart.
And then I'll close this by clicking OK.
So there's our line. The quantity is working well now, but we have this low amount and we want to get rid of that.
All we want to see is this target range, which is that hundred quantity gap between 250 and 350.
Next, I'm going to change the T_Low series so that it's still there, but we can't see it.
So I'll right-click on one of the orange columns and click Format Data Series.
I'll click on the paint can here. For the Fill. I'll select No Fill.
For the Border, No Line.
And I'll close this.
Series is still selected, so we can see it.
When I click away from it, it disappears.
Next, we want to turn this into a solid block, instead of separate columns.
So I'll right-click on one of these grey columns, Format, Data Series.
And under the Series Options, I'm going to change the Gap Width to zero.
So now it all runs together.
And for the fill, I could change it to a different colour.
So maybe I'd like a solid colour, that's a pale yellow, I'll close that.
And for the border, I want no line and click close.
And when I click away, we can see the pale yellow background, which is our target range and the line that shows the quantity of sales each month.
Next I'll change this title. I want it to say monthly sales.
So I'm going to link to that cell.
I'll click on the chart title, click in the formula bar, type, an equal sign ,and click on cell A1, and press enter.
So now it picks up whatever is in that cell
And for the legend at the bottom. I don't need it to show T_Low.
So I'm going to click once to select the legend, then click on T_Low,
press the delete key and that disappears.
So now the legend just shows the target range and the quantity.
So that's a simple line chart with a block that shows the target range.
Show Target Range: Download the Excel Line Chart with Target Range sample file. The zipped Excel workbook is in xlsx format, and does not contain any macros.
Last updated: March 22, 2022 4:01 PM