by Svetlana Cheusheva, updated on
This short tutorial will walk you through adding a line in Excel graph such as an average line, benchmark, trend line, etc.
In the last week's tutorial, we were looking at how to make a line graph in Excel. In some situations, however, you may want to draw a horizontal line in another chart to compare the actual values with the target you wish to achieve.
The task can be performed by plotting two different types of data points in the same graph. In earlier Excel versions, combining two chart types in one was a tedious multi-step operation. Microsoft Excel 2013, Excel 2016 and Excel 2019 provide a special Combo chart type, which makes the process so amazingly simple that you might wonder, "Wow, why hadn't they done it before?".
This quick example will teach you how to add an average line to a column graph. To have it done, perform these 4 simple steps:
In our case, insert the below formula in C2 and copy it down the column:
=AVERAGE($B$2:$B$7)
Done! A horizontal line is plotted in the graph and you can now see what the average value looks like relative to your data set:
In a similar fashion, you can draw an average line in a line graph. The steps are totally the same, you just choose the Line or Line with Markers type for the Actual data series:
Tips:
Adding a line to an existing graph requires a few more steps, therefore in many situations it would be much faster to create a new combo chart from scratch as explained above.
But if you've already invested quite a lot of time in designing you graph, you wouldn't want to do the same job twice. In this case, please follow the below guidelines to add a line in your graph. The process may look a bit complicated on paper, but in your Excel, you will be done in a couple of minutes.
Done! A horizontal target line is added to your graph:
In situations when you want to compare the actual values with the estimated or target values that are different for each row, the method described above is not very effective. The line does not allow you to pin point the target values exactly, as the result you may misinterpret the information in the graph:
To visualize the target values more clearly, you can display them in this way:
To achieve this effect, add a line to your chart as explained in the previous examples, and then do the following customizations:
The screenshot below shows my settings:
To make your graph look even more beautiful, you can change the chart title, legend, axes, gridlines and other elements as described in this tutorial: How to customize a graph in Excel. And below you will find a few tips relating directly to the line's customization.
In some situations, for example when you set relatively big intervals for the vertical y-axis, it may be hard for your users to determine the exact point where the line crosses the bars. No problem, just show that value in your graph. Here's how you can do this:
The label will appear at the end of the line giving more information to your chart viewers:
To improve your graph further, you may wish to add a text label to the line to indicate what it actually is. Here are the steps for this set up:
When finished, remove the chart legend because it is now superfluous, and enjoy a nicer and clearer look of your chart:
If the solid line added by default does not look quite attractive to you, you can easily change the line type. Here's how:
For example, you can choose Square Dot:
And your Average Line graph will look similar to this:
As you can notice, a horizontal line always starts and ends in the middle of the bars. But what if you want it to stretch to the right and left edges of the chart?
Here is a quick solution: double-click the on the horizontal axis to open the Format Axis pane, switch to Axis Options and choose to position the axis On tick marks:
However, this simple method has one drawback - it makes the leftmost and rightmost bars half as thin as the other bars, which does not look nice.
As a workaround, you can fiddle with your source data instead of fiddling with the graph settings:
Now, our graph clearly shows how far the first and last bars are from the average:
Tip. If you want to draw a vertical line in a scatter plot, bar chart or line graph, you'll find the detailed guidance in this tutorial: How to insert vertical line in Excel chart.
That's how you add a line in Excel graph. I thank you for reading and hope to see you on our blog next week!
Table of contents