How To Add Horizontal Lines In Excel

This short tutorial will walk y'all through adding a line in Excel graph such as an boilerplate line, benchmark, trend line, etc.

In the last week'south tutorial, nosotros were looking at how to brand a line graph in Excel. In some situations, however, you may want to draw a horizontal line in another nautical chart to compare the actual values with the target yous wish to achieve.

The task can exist performed by plotting two dissimilar types of data points in the same graph. In before Excel versions, combining 2 chart types in 1 was a tedious multi-step operation. Microsoft Excel 2013, Excel 2016 and Excel 2019 provide a special Philharmonic chart type, which makes the procedure so amazingly unproblematic that you might wonder, "Wow, why hadn't they washed it before?".

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

How to draw an average line in Excel graph

This quick example will teach you lot how to add an average line to a column graph. To have information technology done, perform these iv simple steps:

Calculate the average past using the Average function.
In our case, insert the beneath formula in C2 and copy information technology down the column:

=Average($B$2:$B$7)
Select the source data, including the Average cavalcade (A1:C7).
Go to the Insert tab > Charts grouping and click Recommended Charts.
Switch to the All Charts tab, select the Amassed Cavalcade - Line template, and click OK:

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

In a similar manner, you can describe an average line in a line graph. The steps are totally the aforementioned, you lot simply choose the Line or Line with Markers type for the Actual data series:
Adding an average line to a line graph

Tips:

The same technique can exist used to plot a median For this, use the MEDIAN function instead of Boilerplate.
Calculation a target line or benchmark line in your graph is even simpler. Instead of a formula, enter your target values in the concluding column and insert the Clustered Column - Line combo chart as shown in this case.
If none of the predefined philharmonic charts suits your needs, select the Custom Combination type (the last template with the pen icon), and cull the desired type for each data serial.

How to add together a line to an existing Excel graph

Calculation a line to an existing graph requires a few more than steps, therefore in many situations information technology would exist 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 lot graph, you wouldn't want to exercise the same chore twice. In this case, delight follow the beneath guidelines to add a line in your graph. The process may look a fleck complicated on paper, but in your Excel, you will be done in a couple of minutes.

Insert a new column beside your source information. If y'all wish to describe an average line, fill the newly added column with an Boilerplate formula discussed in the previous example. If yous are adding a benchmark 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 Information… from the context menu:
In the Select Data Source dialog box, click the Add button in the Legend Entries (Series)
In the Edit Series dialog window, do the post-obit:
- In the Series namebox, type the desired proper name, say "Target line".
- Click in the Serial value box and select your target values without the cavalcade header.
- Click OK twice to close both dialog boxes.
The target line series is added to the graph (orange confined in the screenshot below). Correct-click it, and choose Modify Series Chart Type… in the context menu:
In the Change Nautical chart Blazon dialog box, make sure Combo > Custom Combination template is selected, which should exist by default. For the Target line serial, selection Linefrom the Chart Type driblet-downwards 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 unlike values

In situations when yous want to compare the bodily values with the estimated or target values that are unlike for each row, the method described above is not very effective. The line does not permit you to pivot bespeak the target values exactly, as the result y'all may misinterpret the information in the graph:
A target line with changing values

To visualize the target values more than conspicuously, y'all can display them in this manner:
Target values are displayed as small horizontal bars.

To reach this effect, add a line to your chart equally 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 Series pane on the right side of your Excel window.
On the Format Data Series pane, become to Fill & Line tab > Line section, and select No line.
Switch to the Marking section, aggrandize Marker Options, alter it to Built-in, select the horizontal bar in the Type box, and set up the Size corresponding to the width of your confined (24 in our example):
Set the marker Fill to Solid fill up or Blueprint fill and select the color of your choosing.
Set the marker Edge to Solid line and besides choose the desired colour.

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

Tips to customize the line

To brand your graph look even more beautiful, yous tin can alter the chart title, legend, axes, gridlines and other elements as described in this tutorial: How to customize a graph in Excel. And below you lot will find a few tips relating straight to the line'due south customization.

Display the average / benchmark value on the line

In some situations, for example when you ready relatively large intervals for the vertical y-axis, it may be difficult for your users to decide the verbal point where the line crosses the bars. No problem, just show that value in your graph. Hither'south how you tin can do this:

Click on the line to select it:
With the whole line selected, click on the last data point. This will unselect all other information points so that only the final i remains selected:
Right-click the selected data point and option Add Information Label in the context menu:

The characterization volition 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 better your graph further, you may wish to add a text label to the line to indicate what it really is. Here are the steps for this gear up up:

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

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

Change the line type

If the solid line added by default does not await quite attractive to you, you tin hands alter the line type. Here's how:

Double-click the line.
On the Format Data Series pane, go Fill & Line > Line, open the Dash type drop-downward box and select the desired blazon.

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

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

Extend the line to the edges of the chart area

As you lot tin can observe, 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 up the Format Centrality pane, switch to Axis Options and choose to position the axis On tick marks:
Extend the line to the edges of the chart area.

However, this simple method has one drawback - it makes the leftmost and rightmost bars half equally thin equally the other bars, which does not look nice.

Every bit a workaround, you tin can fiddle with your source information instead of footling with the graph settings:

Insert a new row before the first and afterward the last row with your data.
Re-create the boilerplate/benchmark/target value in the new rows and leave the cells in the first 2 columns empty, every bit shown in the screenshot below.
Select the whole table with the empty cells and insert a Cavalcade - Line chart.

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

That's how you lot add a line in Excel graph. I cheers for reading and hope to run across you on our blog side by side week!

You may also exist interested in