I am trying to make a column chart that has a vertical line so the viewer can quickly see how much of the data is to the left and right and how far away the data is from it. The image below demonstrates what I am going for. I added the red line for effect in Paint.

Kc2FBs3.png

I am able to make the blue columns no problem. However when I attempt to add the vertical line, the result always ends up as the following. Note how the vertical line does not get placed where I want it: the 95 mark on the x-axis; which is clearly within the data range. Instead the data gets clumped together to the left and the vertical line flung to the right.

yWpLttj.png

I have read around to find what to do, but pretty much only got results saying to do what I thought I would have to do, which I did:

  1. Highlight the points representing the endpoints of the vertical line (A15:B16).
  2. Select the chart and use Paste Special...
  3. Make sure to choose the option to add cells as a new series, y-values in columns, and x-labels in first column in the popup dialog.
  4. Right click on the new series and choose "Change Series Chart Type..." from the context menu.
  5. Choose the XY Scatter plot with markers. (Keep markers for easier selection)
  6. Right click on the data series and choose "Format Data Series..." from the context menu.
  7. Select the option to plot the series on the primary axis, set markers to none, and make the line a little thinner.
  8. Inspect plot to see it is not as expected.

Please advise.