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)
: 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.
: 1) download and install XY Chart Labeler
, and this pdf tutorial
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".
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)
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
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.
Acknowledgement: The copyright for the adds-in xy scatter plot belongs to
Microsoft kb 213750
you can download the latest version there
Continue to Use bubble size as 3rd dimension
Back to Statistics tutorial home
Excel Analytics Tutorial Home