He Also Put in a Target Range and His Cooler Funny

Show Target Range in Excel Chart

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.

Show Target Range in Excel Chart

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

--Worksheet Chart Data

--Add Target Data

--Format Chart Series

--Show Target in Chart

--Format Target Range

--Get the Sample Workbook

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

Worksheet Chart Data

In this example, there is monthly sales data on an Excel worksheet:

• Month start dates in cells A4:A9
• Monthly sales quantities in cells B4:B9
• Cell A3 is left empty, but a heading could be added there
• Cell B3 has the heading "Qty"

There are records for the first 6 months of the year, and this data will be used to create a line chart.

Add Target Data

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:

• In cells C3, D3 and E3, enter new headings: T_High, T_Low, Target
• In cells C4:C9, enter the high number for each month's target range
  • In this example, the T_High value is 350 each month
• In cells D4:D9, enter the low number for each month's target range
  • In this example, the T_Low value is 250 each month

Next, follow these steps to calculate the Target range amount:

• In cell E4, enter this formula, to calculate the difference between the high and low values:
  • =C4-D4
• Copy that formula down to row 9
• In this example, the result is 100 in each row

Create a Chart

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:

• On the worksheet, select cells A3:B9 -- the heading and data cells for months and quantities
• Next, press the Ctrl key, and select cells D3:E9 - the heading and data cells for T_Low and Target
• DO NOT select the T_High data - it is not needed in the chart

Next, follow these steps to create the chart:

• On the Excel Ribbon, click the Insert Tab
• In the Insert group, click the Insert Line or Area Chart command
• In the drop down menu of chart types, in the 2-D Line section, click on the first option - Line

A stacked column chart is added to the worksheet, near the monthly data.

Tip: You can move and resize the chart after inserting it.

Format Chart Series

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:

• Right-click on one of the Qty segments in the any column of the stacked column chart
• In the pop-up menu, ckick the Change Series Chart Type command
• In the Change Chart Type window, at the bottom, look in the Choose Chart Type
• Tip: If necessary, point to the bottom right corner of the window, and drag to make the window bigger
• Find the Qty series in the list, and click the drop down arrow in its chart type box, that currently says Stacked Column
• In the pop-up menu of chart types, in the Line section, click on the first option - Line chart
• In the centre section of the Change Chart Type window, you can see a preview of the revised chart
• Click the OK button, to complete the chart type change.

The revised chart shows the Qty amounts as a line chart, and the T_Low and Target are still stacked column segments.

Format the Target Range

Next, the two remaining segments in the stacked column need to be formatted:

In the sections below, there are steps to:

• hide the orange series
• push the grey sections together
• (optional) change colour of grey sections

Hide Orange Series

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:

• Right-click on one of the orange segments (T_Low)
• At the bottom of the popup menu, click on Format Data Series
• The Format Data Series pane opens - usually at the right side of the Excel window
• Near the top of the pane, click on the paint can icon (Fill & Line)
• To the left of the Fill heading, click the triangle, to see the Fill options
• Click the No Fill option
• To the left of the Border heading, click the triangle, to see the Fill options
• Click the No Line option

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

Push Grey Series Together

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:

• Right-click on one of the grey segments (Target)
• At the bottom of the popup menu, click on Format Data Series
• Near the top of the pane, click on the column chart icon (Series Options)
• To the left of the Series Options heading, click the triangle, to see the options
• In the Gap Width box, change the setting to 0%
• That will close the gap between the individual columns

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.

Format Target Range

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:

• Right-click on one of the grey segments (Target)
• At the bottom of the popup menu, click on Format Data Series
• Near the top of the pane, click on the paint can icon (Fill & Line)
• To the left of the Fill heading, click the triangle, to see the Fill options
• Click the Solid Fill option
• For the Color setting, click the drop-down arrow, and click on one of the colours
• (optional) If required, change the Border colour too, or set it to No Line

Link Chart Title to Heading Cell

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.

Link Chart Title to Worksheet Cell

To link the chart title to cell A1, follow these steps:

• First, click on the chart title box, to select it
• Next, click in the formula bar, and type an equal sign, to start the formula
• Then, click on cell A1, to create a reference to that cell in the chart title formula
• To complete the formula, press the Enter key on your keyboard.

Now the chart shows the text from cell A1, and will change automatically, if you edit cell A1

Update the Legend

You can also make an optional change to the chart legend, so it does not show the T_Low series.

Follow these steps:

• Click once on the chart legend, to select it
• Next, click on the legend entry for the T_Low series
• Finally, press the Delete key on your keyboard, to remove the selected legend entry

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.

Transcript: Line Chart with Target Range

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.

Worksheet Chart Data

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.

Create a Chart

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.

Show Target in Chart

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.

Format Target Range

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.

Change Chart Title

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

Fix the Chart Legend

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.

Download Sample File

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.

More Chart Tutorials

Cluster Stack Pivot Chart

Charts, Interactive

Charts, Line-Column 2 Axes

Pie Charts

Show Hidden Data in Chart

Waterfall Chart

meekinsimed1954.blogspot.com

Source: https://www.contextures.com/excelcharttargetrange.html

Share this post