Create target line for Excel charts – Quick snapshots

Create target line for Excel charts - Quick snapshots - Techronology

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
Create the column chart - Techronology

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
Add a series for the target line - Create target line - Techronology

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
Overlap target column - Techronology

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

Add the trendline
Add the trendline - Create target line - Techronology

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.

Make trendline linear - Techronology

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
Stretch the trendline - Change forecast - Techronology

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
Hide the target column - Create target line - Techronology

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.

Finalize your target line
Finalize your target line - Techronology

So, we made our target line a solid dark color. You can now format your line in any way you want. It is now part of the chart. Enjoy!


Were you able to do this just from the snapshots? If so, then great! If not, then try again.

Also, you can send us an email at Let us know what you thing, what you want to see, etc.


Tutorials home  Techronology home