Scatter Yourself

I have just spent a very frustrating couple of hours trying to add text labels to a xy scatter chart.  Only to find out that in Microsoft Excel 2010 “there is no built-in command that automatically attaches text labels to data points”. This was as part of the process of editing the European version of our excellent VMS/MSP report created by Bryan Pena and Tony Gregoire.

One of my great frustrations is that finding good technical advice on the web always seems much harder than it should be. Youtube is not a bad place to start. Try searching for scatter graph and you will see what I mean.  Search on Google and you will probably get lost in the Ethernet.

Eventually I came across Microsoft’s own solution which I hope will help you save the time I have wasted. It came with a long warranty which I have put at the bottom of this page. Although this solution involves macros and Microsoft Visual Basic for Applications, persevere it’s much, much easier than as it might seem at first glance.

1. First sort your data as follows (the diagram illustrates this point).

•          The first column should contain the data labels.

•          The second column contains the x values for the xy (scatter) chart.

•          The third and column contain the y values for the xy (scatter) chart.

The table should not contain empty columns, and the column that contains the data labels should not be separated from the column that contains the x values.  Although the example provided contains only one column of data for y values, you can use more than one series of data.

We have attached an example worksheet for you to work with below. Please remember these instructions are designed for users of Excel 2010 only. For early versions of Excel refer back to Microsoft.

2. On the sample worksheet, select the data range (cells B1:C6).

3.  Click the Insert tab, click Scatter in the Charts group, and then select a type.

4. On the Design tab, click Move Chart in the Location group, click New sheet , and then click OK.

5. Press ALT+F11 to start the Visual Basic Editor.

6. On the Insert menu, click Module.

7.  Cut and paste the following code in the module sheet:

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

8. Then close the window

9. Press ALT+Q to return to Excel and make sure you are on the chart sheet.

10. Click the View tab, click Macros  make sure AttachLabels toPoints is highlighted and press Run.

Please don’t contact me to tell me I should have known about this but please do contact me if you know how to do it better. Remember that the technique can work for bubble charts and if you want to get really fancy you can start adding colours and markets to your graph. Learn how to do this on Mathias Brandewinder’s excellent page Excel ScatterPlot with labels, colors and markers.

To down the example sheet click below:

Good Luck!

Please read the following warning 

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Comments

Add New Comment

Post comment

NOTE: Links will not be clickable.
Security text:*

Institute of Economics, Latvian Academy of Sciences

juris hazners 11/18/2013 07:50 am

thanks a lot for the advice which should have come from Microsoft instead!
it took a while to find the best solution you have provided
it helps a lot
juris


Total 1 comments