Getting data labels right....

  • Thread starter Thread starter 43fan
  • Start date Start date
4

43fan

I have an XY Scatter chart plotting two columns of data. The data labels I
want to use for the points on the chart are in a third column, each row
corresponding to the proper row for the data being plotted.

How do I get the data labels to display the values in the third column?

Thanks!
Shawn
 
43fan,

These resources will help:

(1) You can use Rob Bovey's XY Chart Labeler add-in located at:

http://www.appspro.com/Utilities/ChartLabeler.htm

(2) You can use John Walkenbach's Chart Tools add-in located at:

http://j-walk.com/ss/excel/files/charttools.htm

(3) You can use the following macro (load in a standard module):

Sub AddLabels()
Dim RngLabels As Range
Dim Ser As Series
Dim i As Long
If TypeName(Selection) = "Series" Then
Set Ser = Selection
Set RngLabels = Application.InputBox(prompt:="Select the label
range:", Type:=8)
Ser.HasDataLabels = True
For i = 1 To Ser.Points.Count
Ser.Points(i).DataLabel.Text = RngLabels(i)
Next i
Else
MsgBox "Select a series in a chart."
End If
End Sub

In the dialog box, add the reference to the data source like "Sheet1!A1:A5".

(4) You can use the techniques described here:

http://www.pdbook.com/index.php/excel/auto_update_xy_chart_with_independent_data_labels/
 
Not as clean as the prior post, but serviceable is the following. It actually links the datalabels to a cell in the spreadsheet so that you can change the text in your spreadsheet and you'll update the labels inside the chart. Warning, save your spreadsheet before running this macro. If you don't have all the points labeled or some of the points are na# then the macro freezes for some reason and I've never gone to the trouble to fix it. You need to select the series to label before running the macro and you need to have your labels arranged in one column for this macro (but you can easily change it if your labels are all in one row).

Sub AssignPhantomLabels()
Dim lblCurrent As DataLabel
Dim celStart As Range

Set celStart = Range("I26") 'First cell with labels in it
For Each lblCurrent In Selection 'Should have data labels you want to change selected
lblCurrent.Text = "=" & celStart.Address(True, True, xlR1C1, True)
Set celStart = celStart.Offset(1, 0) 'move to the next cell with label text
Next

End Sub
 
Back
Top