Create target line for Excel charts - Quick snapshots

This quick snapshot shows you how to create target line for Excel charts. For the purpose of this quick tip, we will focus on column charts.

Snapshots to create target line for Excel charts

Since these are snapshots, we do not go in-depth on the entire process. Therefore we suggest you have some knowledge of Excel.

Create the column chart

In addition to creating the chart, we made the Gap Width smaller, between columns. Now, the chart looks a little healthier. Generally, we like 70%. Later, we will work with the series overlap option.

Add a series for the target line

For the target line, we are using the average for the three months. After that, we want to overlap the target column on top of the amount column.

Overlap target column

Change the Series Overlap to 100%. This will put the columns on top of each other.

Add the trendline

Just right-click with your mouse on the Target series, and select Add Trendline… from the shortcut menu. You can also click on the series and press keyboard shortcut Shift-F10. We do not discuss the long way here.

You may notice, your trendline may look faint, but it is there. Maybe, you should make it darker. For now, make sure you select Linear, under Trendline Options. This will keep the line straight. And, as long as all the numbers are the same, the trendline should be horizontal.

Stretch the trendline

Select the trendline, and under Format Trendline, change the Forecast for both Forward and Backward to .5. After that, your trendline stretches across the entire plot area. Nice!

Hide the target column

To hide the target column, simply remove the fill and line color. You do not want to just click and delete. Most likely, your trendline will disappear too. Although we did not test that, we are pretty sure.