I need to know how, in Excel, to make a scatterplot with multiple
series, without specifying the ranges by hand.

Example:
Let's say I have the following data

Series    X    Y
apple     1    1
apple     2    4
apple     3    9
apple     4    16
orange    7    9
orange    10   12
orange    14   16
orange    20   22
banana    8    4
banana    16   8
banana    20   10
banana    4    2

I would like a scatterplot that has 12 points on it - 4 points in each
of 3 different colors, the colors corresponding to points from series
apple, orange, and banana.

The catch is that there might be hundreds of points per series, and
perhaps a dozen or two different series. I would like to be able to do
this WITHOUT defining the series ranges by hand - i would like it to
"know" what series to assign a particular row's values to based on the
series column for that row.

Is this possible?

---

Request for Question Clarification by maniac-ga on 12 May 2004 18:28 PDT

Hello Placain,

Do you mind using an Excel macro to assign the ranges? That way - you
have only a single action to take to assign the ranges / generate the
plot.

If not, please indicate the version of Excel you are using to be sure
a macro will work on your system.

  --Maniac

---

Clarification of Question by placain-ga on 14 May 2004 09:26 PDT

A macro would be fine. I am using Excel 2003.

It seems like a Pivotchart would do this, if only it were possible for
a pivotchart to be a scatterplot... I wonder why that isn't allowed?

Hello Placain,

OK. Not sure what can / cannot be done with Pivotcharts, but a macro
works just fine. Let me explain how to add it to your spreadsheet
first and use it; then describe how it works.

Open your spreadsheet w/ Excel. Select
  Tools -> Macros -> Visual Basic Editor
which should bring up a couple windows. If you don't already have a
macro in the file, use
  Insert -> Module
and then copy / paste the macro below into that window.
Close & return to Microsoft Excel. The macro should get saved with the
spreadsheet - and Excel will warn you the next time you open the
spreadsheet that macros are present.

At this point, you should be able to go to the first worksheet (where
I assumed the data is) and use
  Tools -> Macros 
and select the Scatter macro and then select Run. You will likely see
the cursor start to blink a little oddly but eventually the blinking
stops and a scatter chart of your data will appear as a new sheet.

If this doesn't work properly or the remaining explanation does not
make sense - be sure to make a clarification request so I can fix /
explain how it works better. The next section includes some possible
customizations of what the macro currently does.

The macro explanation:
 - CR refers to the "Current Region" starting at cell A1 in the first
worksheet. If your data is not in that position, change the reference
in the Set statement to match your data. You can refer to the
worksheet by name (e.g., Worksheet("Data") or by number).
 - the next few statements create the chart and initialize some
variables used throughout the rest of the code. For example, Title$
will get the value in cell A1 - if you want a different chart title,
change the reference or use a fixed string (e.g,. Title$ = "My Title
Here").
 - the for loop walks through each row of the data. If the value in
the first column changes (after the first row), a new series will be
generated. The name of the series is copied from the first column
value.
 - at the end of the for loop, we add the last series. The code is a
copy of that used above and if you change the series name above - do
it here as well.
 - a final step - add the chart title and disable axes titles. You can
change this to add axis titles if desired.  Change HasTitle for the
appropriate axis to True and then add a line to set AxisTitle.Text in
a manner similar to the ChartTitle. I can provide the exact line if
needed.

If you edit the macro and introduce a bug - I suggest the following to
help diagnose problems:
 - bring up the "Immediate Window" and liberally use print (whatever)
to see what the values are where you got stuck
 - bring up the "Object Browser" - type in a phrase (e.g., Axes), then
select one of the items that appear below and hit the ? button on the
far right to bring up the help file for that specific item. I can
NEVER remember the names of some of these objects (or why it is
ChartTitle.Text) and there are good examples in the help as well.
I used both a lot in preparing this macro and they can serve you well if needed.

Good luck and let me know how it works out.
  --Maniac

' Macro recorded 5/14/2004 by Maniac
'

'   Capture the current region before any other actions
    Dim CR As Range
    Dim MS As Series
    Set CR = Worksheets(1).Cells(1, 1).CurrentRegion
'   Set up the chart
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
'   Zap the series made "for free"
    ActiveChart.SeriesCollection(1).Delete
'   Collect the data and chart each series
    
    Last$ = ""
    This$ = ""
    Title$ = ""
    LastRow = 0
    ThisRow = 0
    For Each rw In CR.Rows
        If rw.Row = 1 Then
            Title$ = rw.Cells(1, 1).Value
        Else
            This$ = rw.Cells(1, 1).Value
            If This$ <> Last$ Then
                ' see if this is the first name seen
                If Last$ <> "" Then
                    ' is not, add the new series
                    Set MS = ActiveChart.SeriesCollection.NewSeries
                        MS.Name = Last$
                        MS.XValues = CR.Range(CR.Cells(LastRow, 2),
CR.Cells(ThisRow, 2))
                        MS.Values = CR.Range(CR.Cells(LastRow, 3),
CR.Cells(ThisRow, 3))
                End If
                Last$ = This$
                LastRow = rw.Row
            Else
                ' save the row number in case it is the last of a set
                ThisRow = rw.Row
            End If
        End If
    Next
    ' Don't forget the last set of values...
    Set MS = ActiveChart.SeriesCollection.NewSeries
    MS.Name = Last$
    MS.XValues = CR.Range(CR.Cells(LastRow, 2), CR.Cells(ThisRow, 2))
    MS.Values = CR.Range(CR.Cells(LastRow, 3), CR.Cells(ThisRow, 3))
    
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = Title$
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

Wonderful! Works great!

For reference (and for anyone else reading this answer in the future):
The line:
Sub Scatter() 
is missing in the source code below; you need to add it before the
rest of the code, when you're pasting into Excel VB.

Also, the lines starting with MS.XValues and MS.Values are wrapped;
unwrap them when you paste.