MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Constant horizontal line on a chart (based on a single value)

Posted by Skewer on July 18, 2001 11:22 AM

Image the scene, you want a graph showing stuff but there is an ideal value or range of values (as in cost, weight, etc).

How can I get a single value (already calculated elsewhere) or values to generate horizontal lines so at a glance the approach towards (or fluctuation between) certain values is visible?

I could just draw an autoshape over the graph I guess but then how could I get that to move around?! Surely there's a chart method that doesn't involve a hidden row(s) containing solely absolute references to this value in its calculated location?


Posted by Mark W. on July 18, 2001 11:55 AM

Let's assume that your desired Y-intercept value
is in cell Sheet1!$A$1. First, create a defined
name called Sheet1!Y_Intercept_Value with a
reference of ={1,1}*Sheet1!$A$1. Select your
chart area and on the formula bar enter the formula,
=SERIES(,,Sheet1!Y_Intercept_Value,1). Y=A1 should
now be plotted on your chart!

Posted by Damon Ostrander on July 18, 2001 12:43 PM


I would suggest that you do use a Shape (line) object. Just set up a macro that draws the line the width of the plot area (Width property) and aligned with the left side of the plot area (Left property), and use its Top property to align it with the value on the graph's ordinate axis that you want the line to represent. Then call this routine whenever the chart is calculated or resized (chart Calculate and Resize events) because this might cause the axes to change. You could also assign the line itself to the macro so that it would re-calculate its position whenever you click on the line. This way, you could easily update it whenever you want, such as when you drag the chart to a new position, resize it, or modify it in any way that might throw the line out of alignment.

I hope this helps. If you need an example, let me know.


Posted by Skewer on July 18, 2001 1:25 PM

Thanks, but I could do with a bit more help...

This is exactly what I need to do, I think, but
how exactly do I create a defined name (and I
assume I can make 2 for max/min values if I need
a Y axis range marked out).

Just in case it's easy, can the area between two
such lines be shaded? Ignore this if you like, it's
in no way essential...

Thanks again,

Posted by Skewer on July 18, 2001 1:31 PM

Erm, I need an example! This sounds useful since
it looks like it would offer other opportunities
such as shaded areas but would the line (and more
importantly any shading) appear superimposed over
the chart's lines or could it be set to be behind
the chart details... or is that getting too weird?

If you can show me a simple sample macro for
plotting a line as you described, fixed in
dimensions according to the chart area and placed
at a point whose value is in a cell on a separate
sheet, that would be great as I have no clue which
way (macro drawing object vs. chart thing) will be
best and/or easier to remember!

Thanks a lot,

Posted by Mark W. on July 18, 2001 1:50 PM

Let's tackle this one issue at a time...

> how exactly do I create a defined name

Choose the Insert | Name | Define... menu
command. Enter Sheet1!Y_Intercept_Value in
the "Names in workbook field"; enter
={1,1}*Sheet1!$A$1 in the "Refers to" field;
and press [ OK ].

> I assume I can make 2

Sure. Name'em Sheet1!Y_Intercept_Value1 and
Sheet1!Y_Intercept_Value2 if you like.

> can the area between two such lines be shaded

Yes; you just use an Area chart type instead of
a line, and make the fill color of the lesser
value the same as the plot area background color.

Posted by Damon Ostrander on July 18, 2001 10:33 PM

Re: Constant horizontal line on a chart, shading


Yes, either lines or shaded areas could underlie the chart (you would have to turn off the chart area fill, and cells and cell contents would then show thru if not empty or filled white). Either lines or shaded areas could also lie on top of the chart with a semi-transparent fill for areas that would allow the chart to show through. I would be happy to send you an example of each of these if you email me so I have your email address. Make sure to remind me what this is about as I get a lot of email questions.


Posted by Skewer on July 19, 2001 11:07 AM

Thanks for everything! (no message, don't click)