how to create vertical line in excel for multiple charts/multiple y ranges

Question

I am trying to add in vertical lines to my line charts to represent an important data/event, and the way I went about doing so was adding in the max value of the y range for the specific date as seen below and making it into a bar chart.

But the problem is I have over 22 charts that I want to have these vertical lines on, and all of them have different y ranges.

What ends up happening is the lines look either too short or in the case in the bottom, go off range to make the data look too small/unviewable.

Does anyone know any other solutions for adding in vertical lines like seen below, but a solution that doesn't require me to make a unique vertical line for each chart?

Answer 1

It's actually not too hard if you "cheat" and use error bars in an xy scatter (or a line) plot against a secondary vertical axis.

1. Plot your "vertical lines" series against the secondary axis.
2. Then, use error bars on that "vertical lines" series.
3. Set the "percentage" of the Error Amount to 100%.
4. Next, set the secondary axis limits between 0 and 1.
5. Suppress the data points of the "vertical lines" series by selecting the series marker option to be "none".
6. And then you can suppress the secondary axis labels and markers to clean it up.

Here's an intermediate view of the above process with steps 1 and 2 done. The "Dividers" series in column C is the one we're interested in; the values are set to 1 at the desired dates, and left blank elsewhere. Note that Excel defaults the error bar percentage to 5%, the secondary axis limits have not yet been altered, and the series marker has not yet been set to "none":

And here's how it looks when all done (except for the secondary axis suppression):