<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Adding a horizontal line to an Excel chart

Published on
49,831 Points
43,731 Views
1 Endorsement
Last Modified:
Approved
Introduction
You may want to add a horizontal line in an Excel 2003 chart to indicate a constant, such as an average, median or threshold value.
This example illustrates how this technique can be used to display additional useful information in your charts.Using Excel 2003, this can be accomplished using the “Error Bar” method as follows:
1) A single point is added to the chart at the correct height, and at one horizontal unit from the y axis.
2) A left-side error bar one unit wide is added to this new data point.
3) The horizontal scale for this series/point only is stretched to fit the width of the chart.
4) Various formatting is applied for consistent results.

Steps
The instructions below assume that you have already created a chart, and would like to add the horizontal line.

Adding a data point to the chart

Right-click chart and select Source Data from the menu that appears.

Click on Series tab, and click the Add button.

Enter the text that you want to appear in the Legend in the Name field. Excel will automatically add the “=” and quotes later.

Enter ={1} in the X Values field.

In the Y Values field, enter a reference to the value at which you want the horizontal line to appear.

Click OK.

Note: If either the X or Y Values field did not appear, you will have to right-click the data point you just added, select Chart Type, and change the type to XY (Scatter). You can then return to this Source Data screen to add the missing data.

A data point added to the chart
The new data point will appear on chart at the correct height for the horizontal line.

Right-click data point and select Format Data Series.

The new data point in the chart
Click on the Patterns tab.

Set the line and marker colour.  Change the marker style to a plus sign.

The line and marker format is set
Click on the Axis  tab.

Select Secondary axis.

Note: Excel incorrectly assumes that we will be creating a secondary Y axis. We will change this to an X axis later.

Changing data series to secondary axis
Adding the Error Bar

Click the X Error Bars tab.

Select a Minus display type.

Enter a fixed error amount of 1.

Adding the error bar
Click the Data Labels tab.

Select Y Value to display a number beside the horizontal line.

Click OK to close the dialog box.

Set value to be displayed beside the bar
Formatting the secondary axis

Right-click on the chart and select Chart Options.

Click on the Axes tab.

Under Secondary axis, check Value (X) axis, and uncheck the Value (Y) axis checkboxes.

Click OK to close the dialog box.

Formatting the secondary axis
Now, we will hide the secondary axis without actually removing it.

Double click the secondary axis in the chart to open the Format Axis dialog box shown here.

Click on the Patterns tab.

Set the three tick mark options to None.

Hide the secondary axis
Next, we will stretch the scale to the width of the chart.

Click on the Scale tab.

Set the Maximum value to 1. This will automatically uncheck the Auto box beside it.

Click the OK button to close the dialog box.

Scaling the bar to stretch the width of the chart
The line is there. All that remains is to format it (optional.)

Double-click on the error bar.

Set the colour and marker as desired.

Click OK.

Change the bar's colour and style
Double-click the data label beside the horizontal line.

The data label
Set the text’s colour, size and font.

Click OK.

Format the label text
The result!

The result
1
Author:pdoelle
2 Comments
LVL 7

Author Comment

by:pdoelle
I welcome any comments you may have on this article, or any suggestions that you may have for further articles in the same vein.
0

Expert Comment

by:ceneiqe
is this the same for Excel 2010 ?
0

Featured Post

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month