How to make YX scatter instead of XY scatter Excel Graph

[charismawelly]

Hi guys,

I need help. I have data such as:

Y X1 X2 X3
1 45 56 71
2 22 33 88
3 44 22 99
4 17 88 19
5 88 44 15
6 66 22 12

If I use XY scatter plot by selecting that all data, I will got plot data like such as 3 horizontal line because excel read 1-6 as X instead as Y.
That I expect is to get 3 vertical line. I can do it manually but I have a lot of data that deal with this problem. It might save my time a lot.
Please share if you have any idea about this. I am really appreciate your help. Thank you.

Regards,
Welly

 

[btomjack]

So you have 3 horizontal lines, but you want 3 vertical lines? This sounds like you just want to switch around the axes (horizontal becomes vertical and vice-versa). Is that correct?

 

[charismawelly]

So you have 3 horizontal lines, but you want 3 vertical lines? This sounds like you just want to switch around the axes (horizontal becomes vertical and vice-versa). Is that correct?

Yup. Is there any trick to get those result? I try to use switch row/column but it didnt work.

 

[btomjack]

I did some looking, and I don't think Excel can do that. To be honest, I'm not sure why the horizontal lines won't work for you. It sounds like the exact same chart, just displayed in a different format.

If it's just that your boss doesn't like the horizontal line format and wants you to switch it for no other reason, I know that feeling and I sympathize.

 

[charismawelly]

I did some looking, and I don't think Excel can do that. To be honest, I'm not sure why the horizontal lines won't work for you. It sounds like the exact same chart, just displayed in a different format.

If it's just that your boss doesn't like the horizontal line format and wants you to switch it for no other reason, I know that feeling and I sympathize.

Actually that's the data related with the high rise building. Sooo, instead of horizontal line, we prefer vertical line because building height is going vertical. Hehe
That's why am looking help here, who know some one develop code or have a trick to solve my problem.. T-T

Thank you btomjack for your effort, I appreciate it so much :D

 

[btomjack]

One option you could do would be to split it up into three charts. It's not an ideal solution, but that could convert them to vertical lines.

Take this data:
Y X1 X2 X3
1 45 56 71
2 22 33 88
3 44 22 99
4 17 88 19
5 88 44 15
6 66 22 12

And change it to this:
X1 Y
45 1
22 2
44 3
17 4
88 5
66 6

X2 Y
56 1
33 2
22 3
88 4
44 5
22 6

X3 Y
71 1
88 2
99 3
19 4
15 5
12 6

 

[charismawelly]

One option you could do would be to split it up into three charts. It's not an ideal solution, but that could convert them to vertical lines.

Take this data:
Y X1 X2 X3
1 45 56 71
2 22 33 88
3 44 22 99
4 17 88 19
5 88 44 15
6 66 22 12

And change it to this:
X1 Y
45 1
22 2
44 3
17 4
88 5
66 6

X2 Y
56 1
33 2
22 3
88 4
44 5
22 6

X3 Y
71 1
88 2
99 3
19 4
15 5
12 6

Yes, it can be solution, but its take time as if I do select the data manually from data sources. Is there any possibilty to rotate the chart if we cant play with the data?

 

[Jon Peltier]

No need to rearrange your data.

You could simply select the Y column, insert your chart, then right click on the chart and choose Edit Data.

Select the existing series, click Edit, and select the range with the X1 values. Click OK once so you stay in the Edit data dialog.

Click the Add button, select the X2 and Y ranges, click OK once.

Click the Add button, select the X3 and Y ranges, click OK twice to return to Excel.

 

[charismawelly]

No need to rearrange your data.

You could simply select the Y column, insert your chart, then right click on the chart and choose Edit Data.

Select the existing series, click Edit, and select the range with the X1 values. Click OK once so you stay in the Edit data dialog.

Click the Add button, select the X2 and Y ranges, click OK once.

Click the Add button, select the X3 and Y ranges, click OK twice to return to Excel.

Yes, I do agree with you. So, if I have 1000 X data, I must do it 1000 times clicking manually right. My question earlier is how to avoid this time wasting by edit one by one.

 

[Jon Peltier]

Well, you could write a program to do this. I did that for myself a long time ago, to accept various input data arrangements other than Excel's default X-Y1-Y2-Y3 to create scatter plots. Y-X1-X2-X3 is one of the options (not limited to 3), as are X1-Y1-X2-Y2-X3-Y3, Y1-X1-Y2-X3-Y3-X3, X1-X2-X3-Y1-Y2-Y3, etc. All I have to do is select the data range, indicate the data layout, and click OK.

I have made this program part of a commercial software package, the Peltier Tech Chart Utility, which you can find using links on my blog. I normally do not mention this product here, because I don't want to take advantage of the forum to spam other members. If anyone complains, or if this reply violates one of the forum rules, then this reply can be deleted.