2

Oy people! My problem is as follows: My data table: enter image description here

Selecting the data and creating a scatter chart nets me such a result: enter image description here

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

This provides me with such a chart: enter image description here

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: enter image description here

  • 1
    if you right click on the chart graph area then click on "Select Data" a window will come up. Depending on your data you may be able to press the button in the middle that says "Switch Row/Column". In your case though I do not think that will work as you have 10 different series with independent X series and all common Y series. – Forward Ed Aug 4 at 7:49
  • @ForwardEd exactly, unfortunately this does not work. – internetofmine Aug 5 at 18:30
  • 1
    Excel is set up to work with with a Common X series for all ranges when doing "automatic" grouping like that. I suspect your options will be to continue with the manual defining, set up a macro/VBA to automate your process, or use a different prg. – Forward Ed Aug 5 at 20:56
  • 1
    However you're switching columns and rows, it isn't switching columns and rows, it's just doing the columns in reverse order or something similar. – fixer1234 Aug 5 at 22:01
  • 1
    Switching columns and rows won't give you what you want because your series are defined by rows. You need to switch the rows referenced as the X and Y values. You could probably do that with VBA. If you don't do much VBA, maybe try recording a macro of switching the X and Y ranges for the first series, then use that as the core to automate doing it for all the series. – fixer1234 Aug 5 at 22:14
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.

Switch XY Data and Before & After Charts

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

  • This seems like a more refined version of the code so I will accept this one, thank you! – internetofmine Sep 9 at 6:31
  • 1
    I'm rewriting it as a tutorial on my blog. The code can be enhanced to bypass errors that occur if the X and Y ranges have multiple areas or if they are hardcoded as arrays in the SERIES formula. I'll circle back and post a link. – Jon Peltier Sep 10 at 12:43
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.

POC

  • 1
    The above code will work until the number of series (rows) exceeds what excel graphing can handle. I do not know that number currently – Forward Ed Aug 5 at 23:52
  • Thank you, your reply is closest to fixing my problem, I did hope for some option I was not aware of that would do it for me, but if there is none, this will do :) – internetofmine Aug 7 at 6:26
  • 1
    I am not a VBA programmer so their may be some more efficient way to code things. I just started by recording a macro of what you were doing manually then tried to modify it to automatically grab the right ranges. It was an interesting learning experience for me. – Forward Ed Aug 7 at 6:40
  • The data is actually outputed from other software, chart is also generated from code (c#), so I will see if there's an option to mimic what you did in my app. – internetofmine Aug 7 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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