CODEWITHSUNDEEP
excelvbachartsgridlines
Eduard Florinescu
Eduard Florinescu

Reputation: 17551

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

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

Upvotes: 2

Views: 15914

Answers (2)

David Zemens
David Zemens

Reputation: 53663

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.

Upvotes: 3

SeanC
SeanC

Reputation: 15923

using vba, you can add a new series:

With ActiveChart.SeriesCollection.NewSeries
    .Values = "={6.9,6.9,6.9,6.9}" 
    'create string beforehand if number and values are unknown
    .ChartType = xlLine
    'and whatever other formatting is needed
End With

not using VBA, you can add a new column to the data, and put all of it equal to the first item, using =$B$2 in each cell to add the line to the graph

Upvotes: 2

Related Questions