Switching X and Y values for every series in scatter chart

Question

Oy people! My problem is as follows: My data table:

Selecting the data and creating a scatter chart nets me such a result:

Then for each series I have go and manually edit them, switching X and Y values.

This provides me with such a chart:

And the chart is ultimately what I want, however it is unacceptable for my needs to do it by hand - is there an option, or quick way of generating the final chart without need for manual switching?

Thanks!

---

Edit. As some suggested I have tried switching rows with columns, unfortunately it does not work, here is the result:

Answer 1

Here's a nicer routine that looks at just the chart series without assuming anything about the data in the sheet. It starts with the series formula, splits it into an array, reverses the X and Y elements of the array, then rejoins the elements into a modified formula, and reapplies the new formula.

Sub SwitchXY()
  If Not ActiveChart Is Nothing Then
    Dim srs As Series
    For Each srs In ActiveChart.SeriesCollection
      Dim SrsFmla As String
      SrsFmla = srs.Formula
      '' =SERIES(name,xvalues,yvalues,number)
      Dim vFmla As Variant
      vFmla = Split(SrsFmla, ",")
      '' array {"=SERIES(name","xvalues","yvalues","number)"}
      Dim temp As String
      temp = vFmla(LBound(vFmla) + 1) ' xvalues
      vFmla(LBound(vFmla) + 1) = vFmla(LBound(vFmla) + 2)
      vFmla(LBound(vFmla) + 2) = temp
      '' array {"=SERIES(name","former yvalues","former xvalues","number)"}
      SrsFmla = Join(vFmla, ",")
      '' =SERIES(name,former yvalues,former xvalues,number)
      srs.Formula = SrsFmla
    Next
  End If
End Sub

Below is some dummy data, an XY chart made from the data with X and Y as Excel plotted them, and the same chart after running the code.

Update:

I've used this question & answer as inspiration, and added some better code to account for more complicated series formulas, in a new tutorial on my blog, Switch X and Y Values in a Scatter Chart

Answer 2

Sub CommonYGraph()

Dim ws As Worksheet
Dim Chart1 As Chart

Dim rowcount As Long
Dim colcount As Long
Dim x As Long

Dim Yvalues As Range
Dim Series_Name As Range
Dim DataRange As Range

Set ws = ActiveSheet

rowcount = Selection.Rows.Count
colcount = Selection.Columns.Count - 1

Set DataRange = Selection.Offset(0, 1).Resize(rowcount, colcount)

Set Yvalues = DataRange(1, 1).Resize(1, colcount)
Set Series_Name = Selection.Range(Cells(2, 1), Cells(rowcount, 1))

Set Chart1 = ws.Shapes.AddChart.Chart
Chart1.ChartType = xlXYScatterLines

For x = Chart1.FullSeriesCollection.Count To 1 Step -1
    Chart1.FullSeriesCollection(x).Delete
Next x

    For x = 1 To rowcount - 1
        Chart1.SeriesCollection.NewSeries
        With Chart1.SeriesCollection(x)
            .Xvalues = DataRange(1 + x, 1).Resize(1, colcount)
            .Values = Yvalues
            .Name = Series_Name(x)
        End With
    Next x

End Sub

I ran the above code and it generated the graph as per your image more or less (gridlines, legend location, series colour and markers, and x values may be different).

with your selection already made in this case C3 to J3, run the vba code.

The code sizes up your selection adds a chart to the existing sheet, deletes any existing series which may have been generated, then adds each of your series individually with common Y values for varying X values for each series. It also adds the series name.