Excel: How can I add custom gridline to a chart?

excel vba charts gridlines
Eduard Florinescu picture Eduard Florinescu · Apr 17, 2013 · Viewed 12.7k times · Source

How can I add a custom horizontal line that has a label and it is at the exact same level as the first column in the chart (see the screenshot below).

Can this be done in VBA?

enter image description here

Answer

David Zemens picture David Zemens · Apr 17, 2013

This could be done in VBA, or it could be done without VBA:

http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html

This method involves creating a secondary Y-axis, and plotting another series of data in a "line" on the second axis.

This is a fairly clean solution.

Otherwise with VBA you would need add a shape/line to the chart (important to add it to the chartObject and not to the Worksheet).

Then compute the height of points and make the line's .Left = the chart's .PlotArea.Left and make the line's .Width = to the chart's .PlotArea.Width. Then set the line's .Top value based on the chart's .PlotArea.Height minus the "height" you calculated for the point.

Related questions

How can I color dots in a xy scatterplot according to column value?

Consider the following worksheet: A B C D 1 COMPANY XVALUE YVALUE GROUP 2 Apple 45 35 red 3 Xerox 45 38 red 4 KMart 63 50 orange 5 Exxon 53 59 green I have used the scatterplot function in Excel to create the following chart: However, each point in the chart has …

Read More
Excel VBA Script to dynamically add series to chart

I'm trying to dynamically add multiple series to a line chart. I don't know beforehand how many series there are so it needs to be dynamic. What I've come up with but doesn't work is the following: The sheet ActiveSheet (…

Read More
excel vba changing bar chart color for a data point based on point value

I have a some charts that in which the X Values are text and Y Values are numerical. I want to color each bar Red if the Y value for the bar is less than zero, Green if greater than …

Read More