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.
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.
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.
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.
Click on the Patterns tab.
Set the line and marker colour. Change the marker style to a plus sign.
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.
Adding the Error Bar
Click the X Error Bars tab.
Select a Minus display type.
Enter a fixed error amount of 1.
Click the Data Labels tab.
Select Y Value to display a number beside the horizontal line.
Click OK to close the dialog box.
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.
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.
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.
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.
Double-click the data label beside the horizontal line.
Set the text’s colour, size and font.
Click OK.
by: pdoelle on 2011-02-02 at 13:49:02ID: 23429