How to switch X-axis to Y-axis in Excel 2013

[phamson5489]

I have made a scatter (X,Y) chart. The number of data series is quite big so i could not input manually then i used " Chart data range" function. However the position of X and Y axis is not as expected . Could you kindly show me how to switch X-axis to Y axis? Thanks a bunch

 

[chobeo31]

As far as I know, now you might have 2 columns with series of data.
Why dont just paste you X-value on right side of Y-value.
I.e, insert new column on Y's right, copy your X value, scatter the data (Y,X) you have then hide created X column.
A bit manually but works.

 

[phamson5489]

As far as I know, now you might have 2 columns with series of data.
Why dont just paste you X-value on right side of Y-value.
I.e, insert new column on Y's right, copy your X value, scatter the data (Y,X) you have then hide created X column.
A bit manually but works.

Thanks for your reply. However it is not that easy. Like i said i used "Chart data range" function. This one automatically understands the first row of the data range are X values and the following rows are Y values. The point is this function allows us to make a chart with one row of X value and many rows of Y value but in my case i have one row of Y value and many rows of X value. I attached my data series and the form of the chart that i want to make as follows

Chart


Hope that you know how to fix this problem it makes me crazy . Thanks a lot

 

[chobeo31]

So you want to make 0, 0.05, 2.55, 7.5, 12.5, 17.5 becomes X variable in this case?
If that is your case, try to copy the whole data set, then use Paste Special with Transpose option ticked.
Does it make sense?

 

[phamson5489]

So you want to make 0, 0.05, 2.55, 7.5, 12.5, 17.5 becomes X variable in this case?
If that is your case, try to copy the whole data set, then use Paste Special with Transpose option ticked.
Does it make sense?

It does not work. Your suggestion is similar to " Switch row/column" function when we select data.
"0, 0.05, 2.55, 7.5, 12.5, 17.5" is automatically understood as X variable by Excel however in my case i want it become Y variable like the chart sample.

 

[chobeo31]

So I misunderstood your idea. If you want to make the chart exactly as the given sample, then you can do like that:

(1) Scatter the data -> you would have hyperbolic-shaped chart
(2) Click on the chart and Select Data (Design in ChartTool tab). Then Edit 7D1,8D1 etc. by switching Series X values and Y values range -> you would have parabolic-shape chart. In this step, I have no idea to make it automatically but change the range on your own. Good luck with that.
(3) Click on your Y axis in the chart now, Format Axis - Axis Options, and tick on Values in reversed order -> you would have the same type of chart you want in the sample after some minor config.

Regards,

 

[phamson5489]

You know what? your solution is exactly what i have done to make the given sample. However the point is the number of data series is too big so i could not do it manually. The data above actually just a part of the whole data. . do you have any other solutions for this problem? Thanks

 

[chobeo31]

So there would be no different choice other than doing that manually, I'm afraid.
However, you can use this workbook given in the link and make the chart you want, then throw it back to your stuffs.
Chart Axes and Axis Tricks
Just download the workbook including Addins to switch XY, plot the graph and config it with options in Addins tab.
Good luck with that.

 

[phamson5489]

oh my goodness, it works. Thank you very much