How To Add Difference In Line Graph In Excel

This brusk tutorial will walk yous through calculation a line in Excel graph such as an average line, criterion, trend line, etc.

In the last calendar week's tutorial, nosotros were looking at how to make a line graph in Excel. In some situations, however, you may want to draw a horizontal line in some other chart to compare the actual values with the target you wish to attain.

The task can be performed by plotting two different types of information points in the same graph. In earlier Excel versions, combining two chart types in one was a boring multi-stride operation. Microsoft Excel 2013, Excel 2016 and Excel 2019 provide a special Philharmonic chart type, which makes the procedure then amazingly simple that you might wonder, "Wow, why hadn't they done it earlier?".

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
- Brandish the average / target value on the line
- Add together a text label for the line
- Change the line type
- Extend the line to the edges of the graph area

How to depict an boilerplate line in Excel graph

This quick example will teach yous how to add an average line to a column graph. To have it done, perform these 4 simple steps:

Calculate the average by using the Boilerplate function.
In our case, insert the below formula in C2 and re-create it downwards the column:

=AVERAGE($B$two:$B$7)
Select the source data, including the Boilerplate 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 ready:
A column graph with an average line.

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

Tips:

The aforementioned technique can be used to plot a median For this, utilise the MEDIAN function instead of Average.
Adding a target line or criterion line in your graph is even simpler. Instead of a formula, enter your target values in the final cavalcade and insert the Amassed Cavalcade - Line combo chart every bit shown in this example.
If none of the predefined combo charts suits your needs, select the Custom Combination blazon (the concluding template with the pen icon), and choose the desired type for each information series.

How to add a line to an existing Excel graph

Adding a line to an existing graph requires a few more steps, therefore in many situations it would exist much faster to create a new combo chart from scratch as explained above.

But if y'all've already invested quite a lot of time in designing you graph, you wouldn't want to practice the aforementioned job twice. In this example, 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.

Insert a new column beside your source data. If y'all wish to draw an average line, fill the newly added cavalcade with an Average formula discussed in the previous example. If you are calculation a criterion line or target line, put your target values in the new column similar shown in the screenshot beneath:
Right-click the existing graph, and choose Select Data… from the context carte:
In the Select Information Source dialog box, click the Add button in the Legend Entries (Series)
In the Edit Series dialog window, do the following:
- In the Series namebox, blazon 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 series is added to the graph (orange bars in the screenshot below). Right-click information technology, and choose Change Series Chart Type… in the context menu:
In the Change Chart Type dialog box, make certain Combo > Custom Combination template is selected, which should be by default. For the Target line series, pick Linefrom the Chart Blazon drop-downward 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 want to compare the actual values with the estimated or target values that are different for each row, the method described to a higher place is non very effective. The line does not allow you to pivot point the target values exactly, equally the result you may misinterpret the information in the graph:
A target line with changing values

To visualize the target values more conspicuously, you lot can brandish them in this way:
Target values are displayed as small horizontal bars.

To achieve this effect, add a line to your nautical chart as explained in the previous examples, and then do the following customizations:

In your graph, double-click the target line. This will select the line and open the Format Data Serial pane on the correct side of your Excel window.
On the Format Data Series pane, go to Fill & Line tab > Line section, and select No line.
Switch to the Marker department, aggrandize Marker 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):
Fix the mark Fill to Solid fill or Pattern fill and select the colour of your choosing.
Set the marker Edge to Solid line and also choose the desired color.

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

Tips to customize the line

To make your graph await even more beautiful, you tin change the nautical chart title, legend, axes, gridlines and other elements equally described in this tutorial: How to customize a graph in Excel. And beneath yous will detect a few tips relating directly to the line's customization.

Display the average / criterion value on the line

In some situations, for example when yous set relatively big intervals for the vertical y-axis, it may be hard for your users to determine the verbal signal where the line crosses the bars. No problem, just prove that value in your graph. Here'south how yous can do this:

Click on the line to select it:
With the whole line selected, click on the last information betoken. This will unselect all other data points so that only the last i remains selected:
Right-click the selected data indicate and pick Add Data Label in the context card:

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

Add a text label for the line

To improve your graph farther, yous may wish to add a text label to the line to signal what it actually is. Here are the steps for this ready:

Select the last data point on the line and add a data label to it every bit discussed in the previous tip.
Click on the label to select it, then click within the label box, delete the existing value and blazon your text:
Hover over the label box until your mouse pointer changes to a 4-sided arrow, and and so drag the label slightly higher up the line:
Right-click the label and choose Font… from the context menu.
Customize the font style, size and colour equally you lot wish:

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

Alter the line type

If the solid line added by default does not expect quite attractive to y'all, you can easily change the line type. Hither'south how:

Double-click the line.
On the Format Information Series pane, become Fill & Line > Line, open the Dash type drop-down box and select the desired type.

For example, y'all can choose Square Dot:
Square Dot line

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

Extend the line to the edges of the chart surface area

Equally yous can notice, a horizontal line e'er starts and ends in the middle of the confined. Only 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 centrality to open the Format Centrality pane, switch to Axis Options and cull to position the centrality On tick marks:
Extend the line to the edges of the chart area.

Nevertheless, this uncomplicated method has one drawback - it makes the leftmost and rightmost confined one-half as thin every bit the other bars, which does not look overnice.

As a workaround, you tin can fiddle with your source information instead of piddling with the graph settings:

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

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