1

I don't understand how to perform a simple 90 degree axis manipulation in Excel.

Here is my data:

╔════╦══════╦═══════╗
║ Y  ║  X1  ║  X2   ║
╠════╬══════╬═══════╣
║  1 ║ 0.00 ║ -1.00 ║
║  2 ║ 0.50 ║ 0.00  ║
║  3 ║ 0.67 ║ 0.33  ║
║  4 ║ 0.75 ║ 0.50  ║
║  5 ║ 0.80 ║ 0.60  ║
║  6 ║ 0.83 ║ 0.67  ║
║  7 ║ 0.86 ║ 0.71  ║
║  8 ║ 0.88 ║ 0.75  ║
║  9 ║ 0.89 ║ 0.78  ║
║ 10 ║ 0.90 ║ 0.80  ║
╚════╩══════╩═══════╝

And here is what the default scatter looks like:

Excel default - please click me

Excel has correctly picked up my two series (X1 and X2) but I want to flip the X and Y axes. The result should look like this:

Excel edited - please click me

I had to create that by manually editing the X and Y values for each series within Excel's Select Data... dialogue. That's fine for one or two series, but what if I want to plot 100s of series? I can't possibly reconfigure them all manually. So how do I format my data so that Excel treats the first col as Y values, and each subsequent col as the corresponding X value of each new series? Thanks

  • 1
    See my answer in the linked duplicate post, which is a VBA routine to plot scatter charts from data arranged like yours. – Jon Peltier May 15 '16 at 1:07
2

I don't think there is a way to do this in excel.

Using vba a macro like this would work. This will swap all the series in the active chart. No error checking or anything included.

Sub swap()
Dim seriesformula() As String

For Each mySeries In ActiveChart.SeriesCollection
    seriesformula() = Split(mySeries.Formula, ",")
    mySeries.Formula = seriesformula(0) & "," & seriesformula(2) & "," & seriesformula(1) & "," & seriesformula(3)
Next

End Sub
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.