Peltier Technical Services, Inc.
- Custom Excel Solutions

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel
- Site Index and Search - RSS

Horizontal Line Across a Column Chart.

Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.
 

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.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. 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