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:

Trial	X	Y
1	504	340
2	507	338
3	518	335
4	527	333
5	539	331
6	549	328
7	560	321
8	566	319
9	571	316

<tbody>
</tbody>

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!