EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

Tips to Add data labels to xy scatter plot chart
Tip to use bubble size to show the 3rd dimension

"Sometimes a small improvement/adjustment can make big changes, generating much more human-looking chart.""            --LDD(Little Dumb Doctor)

Question 1: It's relatively easy to generate an XY Scatter plot like the one in
the image below, the challenge is how to add so many labels to data points
such that they would start to make more sense when a human looked at them.

2) open your Excel sheet and click on the new "XY Chart Labels" menu that appears.
3) click on "Add Labels" in order to determine the range to use for your labels.
4) In the dialog that appears, select the range where your labels will be coming from.
In case if you are using company's computer and don't have the authorization to run this exe file, you can use the following trick:

1) Create the basic xy scatter plot based on the data. (1st column is always the label, 2nd column=x axis, 3 column=y axis)
2) Run the following VB code. (Developer --> Visual Basic --> Insert --> Module, then copy the following code:) If you can't see the "developer" tab in your tab, click: File --> Options --> Customize Ribbon --> Main tabs --> Check "developer".
```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.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub
```
3) In the VB editor, click "Run Sub/userform" or F5.

Tip: Sometimes there are some overlap between the bubbles, if you want to "separate" them, you can sort the data in different order, then use the transparent feature to show both.