Adding a horizontal line to an Excel chart

pdoelle
CERTIFIED EXPERT
Published:
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
45,471 Views
pdoelle
CERTIFIED EXPERT

Comments (2)

CERTIFIED EXPERT

Author

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

Commented:
is this the same for Excel 2010 ?

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.