How to use a VBA macro to add labels to data points in an XY (scatter) chart or in a bubble chart in Excel 2007

Microsoft Office Excel 2007 does not include a built-in command that automatically attaches text labels to data points in an XY (scatter) chart or in a bubble chart. However, you can create a Microsoft Visual Basic for Applications (VBA) macro that adds data labels to both of these chart types.

This article contains a sample VBA macro that adds data labels to an XY (scatter) chart. You can also use this macro to add data labels to a bubble chart.

The sample macro in this article requires that you use the following layout in the worksheet that contains the data and data labels:

• The first column contains the data labels.
• The second column contains the x values for the XY (scatter) chart.
• The third column and subsequent columns contain the y values for the XY (scatter) chart.

To use the macro that is described in this article, create a chart using the following data.

Note Although the following data has only one column of data for the y values, you can use additional columns for y values in your own worksheets.Important The data should not contain empty columns. Additionally, do not separate the column that contains the data labels from the column that contains the x values. You must lay out the data labels and the values in exactly the format that this article describes. However, in your own worksheets, the upper-left cell does not have to be cell A1.

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.

To add data labels to an XY (scatter) chart, follow these steps:

1. On the worksheet that contains the sample data, select the cell range B1:C6.
   
   Note Do not select the whole cell range of the sample data. Select only cell range B1:C6.
2. On the Insert tab, in the Illustrations group, click Chart.
3. In the Create Chart dialog box, click the type of XY (scatter) chart that you want, and then click OK. The XY (scatter) chart is added to the worksheet.
4. In the worksheet, click the XY (scatter) chart. Then, on the Design tab, in the Location group, click Move Chart.
5. In the Move Chart dialog box, click New Sheet, name the new chart sheet, and then click OK.
6. Press ALT+F11 to start Visual Basic Editor.
7. On the Insert menu, click Module.
8. Type the following sample macro 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
   

9. Press ALT+Q to return to Excel 2007.
10. Click the Developer tab.
    
    Note If the Developer tab does not appear, click the File tab, click Excel Options, click Personalize, and then click to select the Show Developer tab in the Ribbon check box.
11. On the Ribbon, click Macros.
12. In the Macro dialog box, click the AttachLabelsToPoints macro, and then click Run.
    
    The macro attaches the data labels that are in the cell range A2:A6 to the data points that are on the chart.