Adding an Excel Chart Target Line [Detailed Instructions]


Charts are excellent tools to instantly and visually present information. The visual presentation seems to make the numbers a lot easier to understand. They’re great when comparing a target or budget to a forecast or an actual expense.

A target line is usually a straight line on a chart that indicates the target, goal or budget. It allows a quick visual comparison to be made to see if the target is being met or exceeded.

Optionally, a target line can also be variable and not a straight line on the chart. This depends on what data you are using, and what you want to show. For example, if you have several different, separate lines, each with their own budget and actual spend, and you want to show them all together on the chart, you can show a different target for each budget line on the same chart.

Excel 2013 and Excel 2016, makes the process of adding a target line very simple and easy. Combo charts were added in those versions. Here you will see how the combo chart makes the process of adding a target line to a chart very simple.

How to show those types of charts in Excel 2016 will be shown below. Both how to build a chart with a single target line, and how to build a chart with multiple target lines will be illustrated. There will be two different methods shown to create multiple target lines.

This post is a little long with different sections, so here are links to the different sections:

Single Straight Target Line

Assume you have a fixed budget or target for each month of the year, and variable expenses each month. This is quite a typical scenario.  So, a chart with a straight line showing the budget across the chart with columns each month showing what was actually spent is an ideal way to present the data.

Here’s the data that will be used for a straight line target chart. Notice that there are twelve months, the target is the same for those twelve months, and the actual amount changes each month.

sample data for a chart target line

Sample Data for Creating a Target Line

To create a chart with a target line, you simply need to add a combo chart. Very quick and simple so far.

When you insert the combo chart, you are presented with options to decide what type of chart styles you want to combine and what values from the selected data will assigned to each chart style. This is where Excel 2016 makes it very simple.

Here’s how to do it:

Step 1

Click somewhere in the data, or highlight the whole region of data.

Highlight Data for Target Line

HIghlight the Data for the Target Line

Step 2

Click insert Combo Chart under Charts

Insert combo chart for target line

Click Insert Combo Chart

Step 3

Click the Create Custom Combo Chart… option

create custom combo chart for target line

Choose Create Custom Combo Chart

Step 4

Change the target series to a line chart, and change the Actual series to a column chart. If any of the series have Secondary Series checked off, remove the check mark. Both series need to be on the same axis, or the results for this chart will not be correct.

select chart types for target line

Select Chart Types for Different Series

Step 5

Sit back and enjoy your new chart!

The target now shows as a line on the chart, and the actual expenses for each month can now be easily visually compared to the target.

chart showing target line

End Result Chart with Target Line

Multiple Target Lines

The previous example showed how to show a single, fixed target or budget line. This works great when looking at a single line of expenses with a fixed budget. However, what if you had a few separate lines each with their own budget or target that you wanted to compare on one chart?

Looking at the sample data shown below, you can see that there are eight different projects, each with their own budget and actual expenditures.

data for multiple target lines

Sample Data for Multiple Target Lines Chart

The process to show multiple target lines is quite similar to the process to show a single target line, as demonstrated above.

There are two different ways to show the budget or target line when showing multiple lines on a chart. You can use markers or you can use error bars. First the use of line markers will be shown and then later, error bars will be used.

Line Markers

Here’s how to do it:

Step 1

Click somewhere in the data, or highlight the whole region of data.

sample data multiple target line

Sample Data for Creating Multiple Target Lines

Step 2

Click insert Combo Chart under Charts

insert combo chart for target line

Click Insert Combo Chart

Step 3

Click the Create Custom Combo Chart… option

create custom combo chart for target line

Choose Create Custom Combo Chart

Step 4

Change the Budget series to a Scatter chart, and change the Actuals series to a column chart. If any of the series have Secondary Series checked off, remove the check mark. Both series need to be on the same axis, or the results for this chart will not be correct.

select scatter and clustered column for multiple target line

Select Chart Types for Scatter and Clustered Column

Step 5

Sit back and enjoy your new chart!

The budgets for each project now show as dots on the chart, and the actual expenses for each project can now be easily visually compared to the budget.

chart with budget target line

End Result Chart With Multiple Budget Lines

Changing the Marker Type

The marker dots for the budget can be changed to a variety of shapes. To do that, right click one of the marker dots, and then select Format Data Point. After selecting Fill & Line, Click Marker, and then click the arrow beside Marker Options. Make sure the radio button beside Built-in is selected, and then select the type of marker you want. You can also change the size of the marker. The image below shows the available markers that you can choose from and how to select them.

marker lines for target lines chart

Available Marker Lines

Below is the same chart shown above, but with a straight line chosen as the marker, and the size of the marker increased to 14.

budget markers changed target lines

Budget Markers Changed to Lines

Multiple Target Lines Using Error Bars

Depending on the data that you use for the charts or the number of different separated budget and expense combinations that you want to show, line markers may not be the best way to go. Using line markers to show the target could end up making the difference between the budget and actual amounts hard to see since the markers could end up begin very thick. This is due to increasing the size of the marker to make it as wide as or wider than the column also increases it height.

A way to adjust the budget section of the chart to be thinner is to use error bars instead of the markers. This creates a line on the chart for each budget rather than the thick markers. The line for the error bars can be changed for its color, size and much more.

Here’s the error bar method:

Use the exact same process of steps 1 through 5 as outlined above for using line markers and then continue on doing this:

Step 6

Select the series for the budget line by clicking one of the markers. Make sure the whole series is selected. Click Design under Chart Tools on the ribbon. Click Add Chart Element under Chart Layouts on the ribbon. Move the selector over Error Bars. The image below shows this.

adding error bars for target line

Adding Error Bars to the Chart

Step 7

For the data and chart used in this example, both horizontal and vertical error bars were automatically added and the vertical error bars were selected. You only want horizontal error bars, so remove the vertical error bars. To do this, select the vertical error bars, then select Fill & Line and the click the radio button beside No Line. The image below illustrates this.

removing vertical error bar for target line

Removing the vertical error bars

Step 8

The line markers now need to be removed. To do this, select the marker lines. You may need to click out of the chart and then select the marker series. Once selected, change the Marker Options to None, as shown below.

remove line marker for target line

Remove the Line Marker

Step 9

Now the horizontal series of the error bars can be formatted. Select the horizontal error bar series. Change the End Style to No Cap, and change the Error Amount to Custom. Click the Specify Value box and in the pop-up form, change both Positive Error Value and Negative Error Value to .4. The changes are illustrated below.

horizontal error bars for target line

Settings for the Horizontal Error Bars

And here is the result of all that work.

Error bars showing budget for target line

Excel Chart with Error Bars for Budget

Notice, though, that there is no legend showing what represents the budget. This is because the marker was changed to None in step 8 above. A workaround is to draw a line identical to what you set for for horizontal error bar.

Here is the same chart after adding a line as a drawing and moving it onto the chart legend. If the chart is resized or moved, the drawn line for the legend will also need to be resized or moved.

drawn line on chart legend for error bar target line

Chart with a Drawn Line on Legend for Budget

The error bars can be changed in color, size, transparency and dash type. This makes them very flexible and versatile.

Summary

Budget or target lines on chart are great for visually showing the target and whether or not the target is actually be achieved. There are different ways of presenting the target on charts, and can be quite a bit of flexibility available in Excel for changing the charts to present what you want.

If you have any tips or questions, please feel free to comment below.

Leave a Reply