Is it possible to plot both a horizontal line and a vertical line on an Excel chart?

Question

I have some scatterplot values that serve as my primary data series, say:

Zinc(ppm), X Values: 20, 50, 60, 70, ...

Iron(ppm), Y Values: 30, 51, 10, 90, ...

Zinc and Iron both have upper threshold limits on how high they can be, say 50 ppm for Iron and 100 ppm for Zinc. I would like these to be displayed visually with a horizontal line for Iron and a vertical line for Zinc.

Displaying either a horizontal line or a vertical line on a secondary X-axis or secondary Y-axis respectively is easy enough (see Peltier's blog, thousands of threads on Stack Overflow, etc.). But, displaying both at the same time seems impossible. To display a vertical line for example, you would dictate a new data series to be a "Scatter with Straight Lines" chart type, set two X values to 100, and set two Y values as 0 and 1. Voila!

The root problem with displaying both a vertical and a horizontal line seems to be that you cannot split the X values and Y values of a single data series between a primary and a secondary axis. The X and Y values of a single data series have to be either both on the primary axis or both on the secondary axis. This becomes problematic when introducing a horizontal line into my example because this would require me adjusting the secondary X-axis which would affect how the 100 is displayed in the vertical line data series.

I'm currently controlling my graph through VBA, but a solution either by VBA or Excel proper would be appreciated!

Thanks.

Answer 1

There's a great answer here from @TimWilliams regarding how to draw a line on a graph. Your question could be re-phrased, I guess, as how to draw two lines on a graph?

I set-up your example per below and simply selected the range A2:I3 and inserted an XY graph without fiddling about with labels etc. Note I have also included a calculation to get the max of the the two rows for the XY graph. This is because I guess you want to have the threshold line respect the max of the axes of the graph.

So, the code is an extension of Tim's example where we introduce two new series to the graph instead of one. For the second line you switch the use of the XValues and Values properties to get either the x- or y-threshold line.

• For the X Threshold it is at point 50 (intThresholdX) on the x-axis and extends from 0-98 on the y-axis.

• For the Y Threshold it extends from 0-70 on the x-axis and is at point 80 (intThresholdY) on the y-axis.

A picture speaks a thousand words for the result:

Code:

Option Explicit

Sub DrawTwoThresholds()

    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim srs As Series
    Dim intThresholdX As Integer
    Dim intThresholdY As Integer
    Dim intMaxX As Integer
    Dim intMaxY As Integer

    Set ws = ThisWorkbook.Worksheets("data") 'switch to your worksheet
    Set cht = ws.ChartObjects(1) 'assumes one chart is on the sheet
    intThresholdX = 50
    intThresholdY = 80
    intMaxX = ws.Range("K2").Value
    intMaxY = ws.Range("K3").Value

    'create x threshold line
    Set srs = cht.Chart.SeriesCollection.NewSeries()
    srs.Name = ""
    srs.XValues = Array(intThresholdX, intThresholdX)
    srs.Values = Array(intMaxY, 0)
    srs.MarkerStyle = xlMarkerStyleNone
    srs.Border.Color = vbRed

    'create y threshold line
    Set srs = cht.Chart.SeriesCollection.NewSeries()
    srs.Name = ""
    srs.XValues = Array(0, intMaxX)
    srs.Values = Array(intThresholdY, intThresholdY)
    srs.MarkerStyle = xlMarkerStyleNone
    srs.Border.Color = vbRed

End Sub

Answer 2

Thanks for your help Robin! The main problem I have with your code is that the user still has to manually contract the axis to make the lines seem like they extend towards infinity.

I ended up setting all of my data series to be on the same axes, and defining the X and Y threshold to very high numbers (e.g. 500000). Afterward I set the axis limits by multiplying either the maximum number of my dataset by 1.1 or the user defined limit by 1.1, whichever one is bigger.

Your solution is probably more code elegant and requires less resources, but I am a neat freak when it comes to chart formatting :D

Horz(1) = 0
Horz(2) = 500000
Vert(1) = 0
Vert(2) = 500000

'First Example Data Series
With ActiveChart.SeriesCollection.NewSeries
    .Name = ActiveSheet.Cells(1, 2) & " Max Old"
    .ChartType = xlXYScatterLines
    .AxisGroup = xlPrimary
    .XValues = "='Graph'!$AE$3:$AE$4"
    .Values = Vert
    .Select
    .Format.Line.Weight = 2.25
    .Format.Line.Visible = True
    .Format.Line.ForeColor.RGB = RGB(195, 214, 155)     'Light Green
    .Format.Line.DashStyle = msoLineDash
    .MarkerStyle = -4142
End With

'Second Example Data Series
With ActiveChart.SeriesCollection.NewSeries
    .Name = ActiveSheet.Cells(2, 2) & " Max Old"
    .ChartType = xlXYScatterLines
    .AxisGroup = xlPrimary
    .XValues = Horz
    .Values = "='Graph'!$AE$5:$AE$6"
    .Select
    .Format.Line.Weight = 2.25
    .Format.Line.Visible = True
    .Format.Line.ForeColor.RGB = RGB(217, 150, 148)     'Light Red
    .Format.Line.DashStyle = msoLineDash
    .MarkerStyle = -4142
End With

With ActiveChart
    'Set the X axis limit
    .Axes(xlCategory, xlPrimary).MinimumScale = 0
    .Axes(xlCategory, xlPrimary).MaximumScale = WorksheetFunction.RoundUp(Application.Max(ActiveChart.SeriesCollection(1).XValues) * 1.1, 0)
    'Set the Y axis limit
    .Axes(xlValue, xlPrimary).MinimumScale = 0
    If Application.Max(ActiveChart.SeriesCollection(1).Values) >= Application.Max(ActiveChart.SeriesCollection(5).Values) Then
        .Axes(xlValue, xlPrimary).MaximumScale = WorksheetFunction.RoundUp(Application.Max(ActiveChart.SeriesCollection(1).Values) * 1.1, 0)
    Else
        .Axes(xlValue, xlPrimary).MaximumScale = WorksheetFunction.RoundUp(Application.Max(ActiveChart.SeriesCollection(5).Values) * 1.1, 0)
    End If
End With

Graph