CODEWITHSUNDEEP
excelchartsvba
Dr. Manuel Kuehner
Dr. Manuel Kuehner

Reputation: 433

Excel: Add labels to data points in XY chart

I want to have labels next to data points in an Excel chart. There is a VBA code from Microsoft for this purpose:

http://support2.microsoft.com/kb/914813/en-us

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

It works so far. But only if the collection has no name:

enter image description here

When I name the collection then the macro returns an error:

enter image description here

Does anyone know how to use the code provided by Mircosoft and still be able to name the data collection?

Upvotes: 2

Views: 10853

Answers (4)

Jon Peltier
Jon Peltier

Reputation: 6063

Excel 2013 introduced the capability to label a chart series with data from cells, after many years of users begging for it. Select the series, and add data labels. Select the data labels and format them. Under Label Options in the task pane, look for Label Contains, select the Value From Cells option, and select the range containing the label text.

enter image description here

And even before this, you could use a free add-in called the XY Chart Labeler (which works on all charts that support data labels, not just XY charts), which you can download from Applications Professionals. It's written by Rob Bovey, a former Microsoft Excel MVP.

Upvotes: 3

nicolas dejean
nicolas dejean

Reputation: 431

There already are some good answers like ZAT's one, explaining how to add labels to a data point in native Excel with VBA language.

But if you don't know anything about VBA it might be difficult to understand. For complex charts like this one I prefer to use Javascript which I think is more "readable" than VBA. And if you want to make a dynamic and interactive chart, javascript comes with a lot of powerful libraries.

Here is a working code I have written for you, with plotly.js (the documentation is very good for js beginners) :

https://www.funfun.io/1/#/edit/5a60bbe7404f66229bda3e39

So to build this chart I put my data in the embedded spreadsheet, which I can then use in my javascript code thanks to a Json file.

I can create a scatter plot like so :

var trace1 = {
  x: firstX,
  y: firstY,
  text: firstLabel,
  mode: 'markers+text',
  textposition:'top right'
};

The firstX and firstY variable are the X and Y values.

To add a label to each point I added a label to text and changed the mode to marker+textinstead of just marker.

Once you've made your chart you can load it in Excel by passing the URL in an Excel add-in called Funfun.

Here is how it looks like:

final

Disclosure : I’m a developer of funfun

Upvotes: 2

Elizabeth
Elizabeth

Reputation: 21

I had the same problem. All you need to do is replace the hardcoded '9' with 'InStr(xVals, ",")' and it will accept any length SERIES name in the field before the first comma.

Upvotes: 2

ZAT
ZAT

Reputation: 1347

Try this after chart generation (assuming chart in the same sheet): (modify this according to your need)

Option Explicit
Sub RenameChartDataLabel()

Dim rngDLabel As Range
Dim iii as integer, pp as integer, dlcount as integer

Set rngDLabel = ActiveSheet.Range("A2:A6")    'change range for datalabels text
ActiveSheet.ChartObjects("Chart 2").Activate  'change chart name
dlcount = ActiveChart.SeriesCollection(1).DataLabels.Count
iii = 1
pp = 1

For iii = dlcount To 1 Step -1
ActiveChart.SeriesCollection(1).DataLabels(iii).Select
Selection.Text = rngDLabel(pp).Value
Selection.Font.Bold = True
Selection.Position = xlLabelPositionAbove
pp = pp + 1
Next
Set rngDLabel = Nothing
End Sub

enter image description here

Upvotes: 1

Related Questions