cellMatrix.net

Add Labels to Chart Data Points

In Excel there is no way to automatically attach text labels to data points within a chart. However, Microsoft has provided a VBA procedure to do so via it's Knowledge Base Article 213750.

The macro can be shortened to the following. To run the macro, first copy it to a standard module. Next, activate the chart and run it.

Sub AddLabels()
    Application.ScreenUpdating = False
    Dim Rng As Range
    Dim Cht As Chart
    Dim i As Integer
    Set Cht = ActiveChart
    Set Rng = ActiveSheet.Range("A1:A10")
    Cht.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowLabel
    Pts = Cht.SeriesCollection(1).Points.Count
    For i = 1 To Pts
        Cht.SeriesCollection(1).Points(i).DataLabel.Text = Rng(i)
    Next i
    Application.ScreenUpdating = True
End Sub

To add the functionality of automatically updating the data labels whenever the cells are changed, the macro can be slightly modified to the following:

Replace

Cht.SeriesCollection(1).Points(i).DataLabel.Text = Rng(i)

with

Cht.SeriesCollection(1).Points(i).DataLabel.Text = "=" & Rng(i).Address(, , xlR1C1, True)

Finally, if you don't feel like fooling around with code, there are two free utilities available that allow you to attach text labels to data points within a chart:

• John Walkenbach's J-Walk Chart Tools
• Rob Bovey's XY Chart Labeler