How to add a secondary X axis to an Excel scatter plot?

Question

Please have a check at the following Excel chart.

I have a problem in plotting (x-y scatter plot) series using 2 horizontal axis, one being a date ("date no") and the other one a calculated person age at that date. Series 1 is on the primary vertical axis while Series 2 is on the secondary vertical axis.

The problem I have is plotting the 2nd horizontal axis ("age") corresponding to the "date no".

I had a similar question in the past but found the solution too complex to implement, There must be a more straightforward solution. Tks.

Answer 1

Indeed, a dummy series would be the way to do that.

Answer 2

I basically did what I described in my first reply, except that I used this for series 2:

=SERIES(Sheet1!$A$5,Sheet1!$B$3:$I$3,Sheet1!$B$5:$I$5,2)

Answer 3

I'm afraid I don't really understand what you want.

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 4

Thank you HansV!

From your reply I start to be sure there is a much smarter way that I am trying to plot my data and you might suggest something. Sorry to let you spend time on something which may not make any sense!

In any case, the link is here: https://1drv.ms/x/s!Ak3dSUA99okYgjDKFmj7AapwbDty

You have two plots of the same data, one plotted “by date” and one “by age”.

I would like to have Excel to include automatically, in the plot “by date”, a secondary H axis where the min value is 73.7 (age corresponding the date 02/03/2014) and the max is 76.4 (age corresponding to 26/11/2016).

Note that in the second plot “by age” I could not get automatically 73.7 and 76.4 as min and max values. I did not look how to force that.

Thank you!

Answer 5

See if the version I uploaded to DropBox does what you want:

https://www.dropbox.com/s/umfpy68amq80iim/TEST%20for%20MS%20Community.xlsx?dl=1