Plot XY Vertical Line on Secondary X Axis

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
149
Hi all,

Using M365 (frmr O365)

I have a line chart with multi line x axis J,F,M,A, M, J, J, A, S, O, N, D | Year
Now I would like to plot vertical lines at 12/31/2019 (Data Element #108 and Max Date (7/15/2020 as of today - Data Element #115)

I tried using Date and Data Element # on Secondary Axis but neither are plotting the XY lines in the correct position

Any ideas?

Thanks
lou

stock.png
stock.png
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,156
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The stock data must be monthly, right? The X values for your lines are slightly off. The first stock value is plotted in the middle of the first month, so it's really at position 0.5. So the first vertical line is at 107.5 (not 108) and the second is at 114.5 (not 115).

Then you need to set your secondary horizontal axis scale. It should have a minimum of 0 and a maximum of 120 (not the automatic values of 107 and 116). Then of course, you should hide the secondary horizontal axis (format it to use no markers and no line). You can hide the secondary vertical axis, or if you keep it visible, use the same number format as the primary vertical axis.
 

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
149
Thanks Jon,

It's looking much better.
I added some markers so I could add data labels for the values for 12/31/2019 as well as current (7/16/2020 here)
The markers do not quite align with the intersection of the line chart and the vertical lines.
I played around with the values for the vertical lines a bit - but nothing quite lines everything up nicely.

Any thoughts?

Thanks,
lou

stock2.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,156
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You should add another XY series to the chart, using the same X values as the secondary axis series that provide the vertical lines, and Y values corresponding to the line chart series on the primary axis. Leave the secondary axis in the chart but hidden. Delete the secondary Y axis, and the secondary axis points will be plotted along the primary Y axis. Alternatively, keep the secondary Y and scale it to match the primary Y axis.
 

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
149

ADVERTISEMENT

Hi,

I took a new approach to my Stock Chart.
I pulled data using the new Stockhistory function

I then created a table from 1/1/2011 - 12/31/2020 and added a formula to return the value from Stockhistory, if no value, then NA().
I then added several series to a chart

One of the Series (Scatter with Straight Lines) appears to have a ghost mark of some type that I cannot remove
I have tried formatting the Data Series several times but I can't get the image to go away.

I tried removing and re-adding the series. I still see the ghost mark.
Circled red below

Thanks,
lou

stock3.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,156
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Could you show what the data looks like, at least in the 2019-2020 range?
Could you indicate what series there are, what data they use, what types they are, and which axes they are on?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,023
Messages
5,834,996
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top