Scatter diagram of more than one series

outtahere

New Member
Joined
Oct 29, 2009
Messages
39
Hi I would like to present a scatter diagram showing performance on one axis and volatility on the other. I then need to do this for several items. I have used the chart to try this but I get Series 1 and a great scatter diagram but I do not know how to get the left hand labels relating to the two figures on the right of it.

Perf vol
FSAP 1.5 3.4
BED 1.3 4
SUKA 3.3 4
AEM 3.9 3.7
MGR 6 3.6
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Surprisingly, there isn't a simple way to do what you want.

Here are three options:

1) Select the numbers in the 2 columns. Create a XY Scatter chart.

Then, use a 3rd party product -- Rob Bovey's freeware XY Chartlabeler from www.appspro.com or my own shareware product TM Chart Utilities (http://www.tushar-mehta.com/excel/software/chart_utilities/index.html) to add the items in column 1 as the labels for the data points.

Optionally, change the settings for the chart series to show each data point in a different format.

2) Create a chart from the 2 numbers in row 2 (row 1 has headers). Use the chart UI to add the 1st column in row 2 as the name of the series. Repeat the process for every row.

3) Use the code below to implement 2 above.

Code:
Option Explicit

Sub addScatterChartByRow()
    Dim WS As Worksheet: Set WS = ActiveSheet
    Dim Rng As Range: Set Rng = Selection.CurrentRegion
    Dim Cht As Chart
    Set Cht = Charts.Add
    With Cht
    Do While .SeriesCollection.Count > 0: .SeriesCollection(1).Delete: Loop
    .ChartType = xlXYScatter
    Set Cht = .Location(Where:=xlLocationAsObject, Name:=WS.Name)
        End With
    Dim I As Integer
    For I = 2 To Rng.Rows.Count
        With Cht
        Dim aSeries As Series
        .SeriesCollection.Add Source:=Rng.Rows(I).Offset(0, 1).Resize(, 2), _
            Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=True, Replace:= _
            False
        Set aSeries = .SeriesCollection(.SeriesCollection.Count)
        With aSeries
        .Name = Rng.Rows(I).Cells(1).Value
            End With
            End With
        Next I
    End Sub

Hi I would like to present a scatter diagram showing performance on one axis and volatility on the other. I then need to do this for several items. I have used the chart to try this but I get Series 1 and a great scatter diagram but I do not know how to get the left hand labels relating to the two figures on the right of it.

Perf vol
FSAP 1.5 3.4
BED 1.3 4
SUKA 3.3 4
AEM 3.9 3.7
MGR 6 3.6
 

outtahere

New Member
Joined
Oct 29, 2009
Messages
39
Surprisingly, there isn't a simple way to do what you want.

Here are three options:

1) Select the numbers in the 2 columns. Create a XY Scatter chart.

Then, use a 3rd party product -- Rob Bovey's freeware XY Chartlabeler from www.appspro.com or my own shareware product TM Chart Utilities (http://www.tushar-mehta.com/excel/software/chart_utilities/index.html) to add the items in column 1 as the labels for the data points.

Optionally, change the settings for the chart series to show each data point in a different format.

2) Create a chart from the 2 numbers in row 2 (row 1 has headers). Use the chart UI to add the 1st column in row 2 as the name of the series. Repeat the process for every row.

3) Use the code below to implement 2 above.

Code:
Option Explicit

Sub addScatterChartByRow()
    Dim WS As Worksheet: Set WS = ActiveSheet
    Dim Rng As Range: Set Rng = Selection.CurrentRegion
    Dim Cht As Chart
    Set Cht = Charts.Add
    With Cht
    Do While .SeriesCollection.Count > 0: .SeriesCollection(1).Delete: Loop
    .ChartType = xlXYScatter
    Set Cht = .Location(Where:=xlLocationAsObject, Name:=WS.Name)
        End With
    Dim I As Integer
    For I = 2 To Rng.Rows.Count
        With Cht
        Dim aSeries As Series
        .SeriesCollection.Add Source:=Rng.Rows(I).Offset(0, 1).Resize(, 2), _
            Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=True, Replace:= _
            False
        Set aSeries = .SeriesCollection(.SeriesCollection.Count)
        With aSeries
        .Name = Rng.Rows(I).Cells(1).Value
            End With
            End With
        Next I
    End Sub
Hi, Tushar, thanks. I would like to wait to see if there is a simpler solution. I realise I did not display the date properly as it does not show the headers above each column of figures. I would prefer not to start writing code as this is not my line of business. Is the shareware idiot proof or do I need to spend hours installing and reading instructions?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top