<

Adding a horizontal line to an Excel chart

Published on
48,458 Points
42,358 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
Comment
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

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month