Peltier Tech
Chart Utilities

Excel Dashboards

Books at Amazon.com

Buy me a coffee

If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.

Horizontal Line Across a Column Chart.

For a newer, more detailed approach, see Add a Horizontal or Vertical Line to a Column or Line Chart or go straight to the new pages:

• Add a Horizontal Line to a Column or Line Chart: Series Method.
• Add a Vertical Line to a Column or Line Chart: Series Method.
• Add a Horizontal Line to a Column or Line Chart: Error Bar Method.
• Add a Vertical Line to a Column or Line Chart: Error Bar Method.

This page has been retained for compatibility with old links.

Example posted after sufficient encouragement from Debra Dalgleish.

How do you add a nice horizontal line on a column chart, to show a target value, or the series average? The method involves adding a new series, applying it to the secondary axes, and making the secondary axes disappear.

Start with a column chart, like this:

1. Enter your value in two adjacent cells.
2. Copy these cells, select the chart.
3. Paste special to add the new series (by row or column depending on their orientation). So far it's just another set of columns, as shown, but we'll fix that.




4. Select the new series (which in this example was drawn as another column series).
5. Go to Chart menu > Chart Type, and select a line without markers.




6. With the series still selected, go to the Format menu > Format Selected Series > Axis tab, and check Secondary Axes.
7. Select the chart.
8. Go to the Chart menu > Chart Options > Axes tab.
9. Check the secondary X axis.
10. Uncheck the secondary Y axis.




11. Select the secondary X axis (at the top of the chart).
12. Go to Format menu > Format Selected Axis > Scale tab.
13. Uncheck the Value (Y) Axis crosses between categories.
14. Then go to the Patterns tab.
15. Make sure "None" is selected for major and minor axis tick marks and tick labels.





There's your column chart with a horizontal line, just the way you wanted. For a zipped workbook that shows this technique, click here.

Peltier Technical Services, Inc.

Commercial Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile