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.

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

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

____________________________________

4 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

3. IF says:

Ok, here’s a challenge:) I have a spreadsheet to track lab results over time. The data itself is pretty basic: a list of appx 100 lab test variables (Glucose, Sodium, Cholesterol, etc) with a Goal/Target reference range for each one, which runs down the left side, and a list of dates across the top (also have a completely transposed version). Each time tests are done, the results are entered in the date column. There’s also a total of 100 date columns set up to accept and calculate new test results.
*If there was a non-macro way to automatically add new columns/rows (and fill/update), rather than have a pre-set number of cells, that would be amazing… but that’s a different topic.

The fun part: For any areas of concern, I start a new sheet and insert a line chart so I can see trends. It’s a rolling line chart that automatically plots the 5 most recent test results for the specific variable(s) selected.

The dilemma: For any real value, I also need to see the Goal/Target range on the chart. I know there’s a method using stacked columns to create a floating horizontal band, but every version of that method (that I’ve found) requires three additional values (high, low, and the difference between them) to match each instance of entered data. In my case that means 3 cells for every individual variable, for each date they were tested (appx 300 additional cells per testing date).

The question: The reference ranges for each variable really don’t change over time… I only need to plug in the High & Low value once for each one. Is there a way to use those two values to create the range/floating band… somehow get the chart to interpret two values as a series rather than having to add three additional values for every single data point?

I feel like I’m missing something simple… It’s basically just a high/low range on a chart, right?

The interim solution is to create a separate sheet just for all the new calculations, which has turned out to be more of an elaborate project than expected:/

Thanks for the help!!

4. IF says:

Problem solved… using Excel on Windows, and a secondary horizontal access.