X Labels for XY Scatterplot

[mlauffer]

Hello!

I have coordinate data (specific X and Y value) that I am trying to plot as a scatterplot.
What I am plotting is coordinates for a location for every trial in an experiment, so it would look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Trial[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]504[/TD]
[TD]340[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]507[/TD]
[TD]338[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]518[/TD]
[TD]335[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]527[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]539[/TD]
[TD]331[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]549[/TD]
[TD]328[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]560[/TD]
[TD]321[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]566[/TD]
[TD]319[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]571[/TD]
[TD]316[/TD]
[/TR]
</tbody>[/TABLE]

I can plot my coordinates just fine, but the field that allows you to label x data points in the "select data" menu is inactive. How can I label each point to indicate it's trial number? Do I need to make each trial a separate series?

Thanks!

 

[Domenic]

First, place the following code in a regular module (Visual Basic Editor > Insert > Module > Copy/Paste > Close and return to Microsoft Excel). Then, select/activate your XY Chart, and try running the macro...

[COLOR=darkblue]Sub[/COLOR] AddLabelsToXYChart()

    [COLOR=darkblue]Dim[/COLOR] rLabels [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sFormula [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] lPoints [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] ActiveChart [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        MsgBox "Please select/activate an XY Chart, and try again.", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] ActiveChart.ChartType
        [COLOR=darkblue]Case[/COLOR] -4169, 74, 75, 72, 73
            [COLOR=green]'Do nothing[/COLOR]
        [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
            MsgBox "Please make sure the selected chart is an XY Chart, and try again.", vbExclamation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] ActiveChart
        sFormula = Split(.SeriesCollection(1).Formula, ",")(1)
        [COLOR=darkblue]Set[/COLOR] rLabels = Range(sFormula).Offset(, -1)
        .SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=[COLOR=darkblue]False[/COLOR]
        lPoints = .SeriesCollection(1).Points.Count
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] lPoints
            .SeriesCollection(1).Points(i).DataLabel.Text = rLabels(i, 1).Value
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!