Displaying information from scatter plots

**aswan08** · 06-21-2016, 03:58 AM

Hi,

Im sure this has already been answered, but if anyone could help any extra that would be great thanks.

My situation is as follows

Town______Population______ Shops
Uist_________3000___________ 4
Bradie______16000___________13
St Tom_____12500___________ 10

If I am displaying Population vs Shops in a scatter plot, how would I know which point is Uist or Bradie or St Tom? Without actually manually looking up the info. This would be easy for the example above but my data set has many hundreds of points therefore a look up process would be time consuming. I would like to be able to click a point on the scatter plot and see what town I am looking at, usually for outliers on the dataset.

Kind regards
Alistair

**Crooza** · 06-21-2016, 05:17 AM

Here's a macro to link the labels to the town name and I've also attached an example spreadsheet

Sub AttachLabelsToPoints()

   'Dimension variables.
   Dim Counter As Integer, ChartName As String, xVals As String

   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = False

   'Store the formula for the first series in "xVals".
   xVals = ActiveChart.SeriesCollection(1).Formula

   'Extract the range for the data from xVals.
   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
   Do While Left(xVals, 1) = ","
      xVals = Mid(xVals, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals).Cells.Count
     ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
         True
      ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Formula = "='" & ActiveSheet.Name & "'!" & Range(xVals).Cells(Counter, 1).Offset(0, -1).Address
  ' the key to getting the labels right is to change the offset (4 or however many columns AFTER the data column'  Next Counter
Next Counter

End Sub