Adding a reference line in a bar plot

Question

I am trying to add a line to my chart as an indicator, but unable to think of anyway that I can do it.

lrow = ThisWorkbook.Sheets("ABC").Cells(Rows.Count, 1).End(xlUp).Row
Charts.Add
ActiveChart.ChartType = xlBarStacked
ActiveChart.SetSourceData Source:=Sheets("ABC").Range("A2:AE" & lrow), PlotBy:= xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="ABC"
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "XYZ"
ActiveChart.Axes(xlValue).MajorGridlines.Delete
ActiveChart.ChartTitle.Font.Size = 11
Set cht = ActiveChart

Suppose my data is just like

                  John             Jack             Jesse
  Exam 1           5                6                4
  Exam 2           6                7                5
  Exam 3           1                4                9
  Exam 4           3                9                8
  Exam 5           8                6                1

You can imagine how a bar chart will look like. Now how do I add a Vertical line in this graph at suppose a value of 25 which will indicate any bar going above 25 and like serve as distinction or excellency line. So each Bar of total three will now have a name and marks scored in each exam. If you take it the other way we can add up each individual exam to come up with the total. So the line I need to plot will work to show if any of these guys total is more than 25. Hope this explains. Thanks in advance.

Answer 1

1. Add another row at the bottom of your data to specify your goal line for the exams:

    ╔════════╦══════╦══════╦═══════╗
    ║        ║ John ║ Jack ║ Jesse ║
    ╠════════╬══════╬══════╬═══════╣
    ║ Exam 1 ║    5 ║    6 ║     4 ║
    ║ Exam 2 ║    6 ║    7 ║     5 ║
    ║ Exam 3 ║    1 ║    4 ║     9 ║
    ║ Exam 4 ║    3 ║    9 ║     8 ║
    ║ Exam 5 ║    8 ║    6 ║     1 ║
    ║ Goal   ║   25 ║   25 ║    25 ║
    ╚════════╩══════╩══════╩═══════╝

2. Use code similar to the following to create your chart:

   Range("A1:D7").Select
   ActiveSheet.Shapes.AddChart.Select
   ActiveChart.SetSourceData Range("Sheet1!$A$1:$D$7"), xlRows
   ActiveChart.ChartType = xlColumnStacked
   ActiveChart.SeriesCollection(6).ChartType = xlLine
   

It should be pretty similar to what you currently have with the exception that just the last data series (the goal, series 6, in this case) is being changed to a line instead of a stacked bar. You could dynamically insert this value based on the number of rows/series in your source data, similar to what you're doing with lrow.

3. This should create a chart like the following (goal line in red at y-axis 25). If you need more control over the positioning/styling of the goal line, it can be formatted or even plotted on a secondary Y axis.