How to get a drop line from a single data point in a chart?

Question

Hi All,

I am using Mac Excel 16.34 (20020900) and this is my chart:

What I am trying to do is get a drop line like what is shown as the red dashed line.  That is just a line added to the chart that I must move every time that I add data to the chart. I want to do this only for that specific data point, not a range.

THx!

Answer 1

Hi Von,

Per your description, I’ve tested and searched a lot to meet your requirement but sorry to convey that there is no direct way to achieve your requirement. However, during my test, I’ve found a workaround with the help of the Scatter chart and it’s very hard to explain so I recorded a video for you. Here is the link for the recorded video.

Appreciate your patience and understanding.

Best Regards,

Chitrahaas

Answer 2

Hi

Use this sample VBA to turn droplines on

Sub TurnDropLinesOn()

    ActiveChart.ChartGroups(1).HasDropLines = True

End Sub

Once you have turned droplines on, you can right click on the drop lines and format them using the secret Format Drop Lines dialog that I had no idea even existed!

Answer 3

Hi Von,

Per your description, I’ve tested and searched a lot to meet your requirement but sorry to convey that there is no direct way to achieve your requirement. However, during my test, I’ve found a workaround with the help of the Scatter chart and it’s very hard to explain so I recorded a video for you. Here is the link for the recorded video.

Appreciate your patience and understanding.

Best Regards,

Chitrahaas

Chitrahaas,

Thank you for the video, it was very helpful and it got me to a working workaround.

Here is the revised chart:

Since I wanted drop lines connecting to the horizontal axis pegged to the secondary vertical axis, I added a new "Milestones" series to the chart on the secondary axis with values for those 3 points (the first which was negative) that connect with the 3 diamonds I formatted primary (line) vertical axis.

Problem solved.

THx!