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

1 Answer

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