How To Draw A Line On A Excel Graph

This short tutorial will walk you through calculation a line in Excel graph such equally an average line, benchmark, tendency line, etc.

In the final week'south tutorial, we were looking at how to make a line graph in Excel. In some situations, yet, you may want to draw a horizontal line in another chart to compare the actual values with the target you wish to accomplish.

The task tin can be performed past plotting two different types of information points in the aforementioned graph. In before Excel versions, combining two chart types in 1 was a tedious multi-step performance. Microsoft Excel 2022, Excel 2022 and Excel 2022 provide a special Combo chart type, which makes the process so amazingly simple that y'all might wonder, "Wow, why hadn't they done it before?".

Draw an average line in Excel graph
Add a line to an existing Excel chart
Plot a target line with different values
How to customize the line
- Display the average / target value on the line
- Add a text label for the line
- Alter the line blazon
- Extend the line to the edges of the graph area

How to depict an boilerplate line in Excel graph

This quick example will teach you how to add an boilerplate line to a cavalcade graph. To take it done, perform these iv simple steps:

Calculate the average by using the Boilerplate office.
In our case, insert the below formula in C2 and copy information technology down the column:

=AVERAGE($B$two:$B$7)
Select the source data, including the Average column (A1:C7).
Go to the Insert tab > Charts group and click Recommended Charts.
Switch to the All Charts tab, select the Clustered Column - Line template, and click OK:

Washed! A horizontal line is plotted in the graph and you can now see what the average value looks like relative to your data set:
A column graph with an average line.

In a similar fashion, you can draw an average line in a line graph. The steps are totally the aforementioned, you just choose the Line or Line with Markers type for the Actual data serial:
Adding an average line to a line graph

Tips:

The same technique tin be used to plot a median For this, use the MEDIAN function instead of Average.
Adding a target line or criterion line in your graph is fifty-fifty simpler. Instead of a formula, enter your target values in the last column and insert the Amassed Column - Line combo chart as shown in this example.
If none of the predefined combo charts suits your needs, select the Custom Combination type (the terminal template with the pen icon), and choose the desired type for each data series.

How to add a line to an existing Excel graph

Calculation 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 fourth dimension in designing yous graph, yous wouldn't want to do the same job twice. In this case, delight follow the below guidelines to add together a line in your graph. The process may wait a bit complicated on paper, just in your Excel, yous will be done in a couple of minutes.

Insert a new column beside your source data. If you lot wish to draw an average line, fill the newly added column with an Average formula discussed in the previous case. If you are adding a benchmark line or target line, put your target values in the new column like shown in the screenshot beneath:
Right-click the existing graph, and choose Select Data… from the context menu:
In the Select Data Source dialog box, click the Add push button in the Legend Entries (Series)
In the Edit Series dialog window, do the post-obit:
- In the Serial namebox, type the desired name, say "Target line".
- Click in the Series value box and select your target values without the column header.
- Click OK twice to close both dialog boxes.
The target line serial is added to the graph (orange bars in the screenshot below). Right-click it, and cull Alter Serial Chart Type… in the context carte:
In the Alter Chart Blazon dialog box, make sure Combo > Custom Combination template is selected, which should exist by default. For the Target line series, pick Linefrom the Chart Blazon drop-down box, and click OK.

Done! A horizontal target line is added to your graph:
A target line is added to the graph.

How to plot a target line with different values

In situations when you desire to compare the actual values with the estimated or target values that are unlike for each row, the method described in a higher place is not very constructive. The line does non allow you to pin indicate the target values exactly, as the result you lot may misinterpret the information in the graph:
A target line with changing values

To visualize the target values more clearly, you can display them in this mode:
Target values are displayed as small horizontal bars.

To attain this effect, add together a line to your chart equally explained in the previous examples, and then practise the post-obit customizations:

In your graph, double-click the target line. This volition select the line and open up the Format Data Serial pane on the right side of your Excel window.
On the Format Data Serial pane, go to Fill & Line tab > Line department, and select No line.
Switch to the Mark department, aggrandize Mark Options, change information technology to Built-in, select the horizontal bar in the Blazon box, and set the Size respective to the width of your bars (24 in our example):
Prepare the marking Make full to Solid fill or Design fill and select the color of your choosing.
Gear up the marker Edge to Solid line and as well choose the desired color.

The screenshot beneath shows my settings:
Set the marker fill and border.

Tips to customize the line

To make your graph look even more beautiful, yous can modify the chart title, legend, axes, gridlines and other elements as described in this tutorial: How to customize a graph in Excel. And beneath y'all volition discover a few tips relating directly to the line's customization.

Display the boilerplate / criterion value on the line

In some situations, for instance when yous set up relatively big intervals for the vertical y-axis, it may be hard for your users to determine the verbal point where the line crosses the bars. No trouble, just show that value in your graph. Here's how you can do this:

Click on the line to select information technology:
With the whole line selected, click on the last information point. This will unselect all other data points so that just the last ane remains selected:
Correct-click the selected data point and choice Add together Information Label in the context menu:

The label will appear at the end of the line giving more information to your nautical chart viewers:
The label is added to the last point of the average line.

Add together a text label for the line

To ameliorate your graph further, you lot may wish to add a text label to the line to indicate what it really is. Here are the steps for this set upward:

Select the last information bespeak on the line and add together a data label to it as discussed in the previous tip.
Click on the label to select information technology, then click within the characterization box, delete the existing value and type your text:
Hover over the label box until your mouse pointer changes to a four-sided arrow, and and then elevate the label slightly in a higher place the line:
Correct-click the label and choose Font… from the context menu.
Customize the font style, size and color as yous wish:

When finished, remove the nautical chart legend because it is now superfluous, and enjoy a nicer and clearer look of your nautical chart:
An average line with a text label.

Change the line type

If the solid line added past default does not wait quite attractive to you lot, yous can easily change the line blazon. Here's how:

Double-click the line.
On the Format Data Series pane, go Fill & Line > Line, open the Nuance type driblet-down box and select the desired type.

For example, you can cull Square Dot:
Square Dot line

And your Average Line graph volition look similar to this:
A graph with an Average dashed line.

Extend the line to the edges of the chart area

Every bit you can notice, a horizontal line ever starts and ends in the middle of the bars. Merely what if you want it to stretch to the right and left edges of the nautical chart?

Here is a quick solution: double-click the on the horizontal axis to open the Format Axis pane, switch to Axis Options and cull to position the axis On tick marks:
Extend the line to the edges of the chart area.

However, this unproblematic method has ane drawback - it makes the leftmost and rightmost confined half equally sparse equally the other bars, which does not wait nice.

As a workaround, you can fiddle with your source data instead of lilliputian with the graph settings:

Insert a new row before the first and after the last row with your data.
Re-create the average/benchmark/target value in the new rows and leave the cells in the first two columns empty, as shown in the screenshot below.
Select the whole table with the empty cells and insert a Column - Line chart.

Now, our graph clearly shows how far the first and concluding bars are from the average:
Excel graph with an extended average line