Label Each Series in a Chart

In 9 Steps to Simpler Chart Formatting I suggested using data labels to identify each series rather than using a legend. I have a small VBA procedure that I use for this. It labels the last point of each series, and removes other labels. It also has an error trap that skips points that are not plotted because of blank cells or #N/A errors.

Sub LabelLastPoint()
  Dim mySrs As Series
  Dim iPts As Long
  Dim bLabeled As Boolean
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation
  Else
    For Each mySrs In ActiveChart.SeriesCollection
      bLabeled = False
      With mySrs
        For iPts = .Points.count To 1 Step -1
          If bLabeled Then ' series has already received valid label
            ' handle error if point isn't plotted
            On Error Resume Next
            ' remove existing label if it's not the last point
            mySrs.Points(iPts).HasDataLabel = False
            On Error GoTo 0
          Else ' series does not yet have valid label
            ' handle error if point isn't plotted
            On Error Resume Next
            ' remove existing label (linked labels otherwise resist reassignment)
            mySrs.Points(iPts).HasDataLabel = False
            ' add label
            mySrs.Points(iPts).ApplyDataLabels _
                ShowSeriesName:=True, _
                ShowCategoryName:=False, ShowValue:=False, _
                AutoText:=True, LegendKey:=False
            bLabeled = (Err.Number = 0)
            ' 2010 no error if point doesn't exist: label applied, but it's blank
            If bLabeled Then bLabeled = (Len(mySrs.Points(iPts).DataLabel.Text) > 0)
            If Not bLabeled Then
              ' remove blank label
              mySrs.Points(iPts).HasDataLabel = False
            End If
            On Error GoTo 0
          End If
        Next
      End With
    Next
    ' remove legend
    ActiveChart.HasLegend = False
  End If
End Sub

To implement this procedure, follow the steps in How To: Use Someone Else’s Macro.

 

Peltier Tech Charts for Excel

Comments

  1. Hi Jon, me again…I’m sure this is probably a really easy answer but I cannot figure out how to chart the following. I have one series that is a total of 11,175 and the smallest series is a total of 79. I cannot get the 79 to show up because the Y axis is from 0 to 11,175. Any suggestions?

    Darlene

  2. Do you mean you can’t see the data because it’s too close to zero? What kind of chart is it? are the curves plotting the same kind of information?

    To get around this problem you could use a logarithmic scale, put a break in the axis, or plot the curves in two different panels of a panel chart.

  3. I would then add

    ActiveChart.HasLegend = False

    to the very end to remove the now redundant chart legend.

  4. Jaanus – Good idea. I missed it because I probably allready deleted the legend, but I’ve added it to the code above.

  5. Very nice, Jon.
    Still, have you ever pondered on a greater luxury: automatic connecting line between (the last) point and its label? What is your tentative opinion – would it be even soluble by VBA means?

    Petr

  6. Petr –

    IMO, leader lines do not add to a chart, they seem to add clutter. If you need leader lines to clarify which label corresponds to which series, you may be dealing with excessive clutter. When I am almost cluttered enough to use leader lines, I try to rely instead on coloring the label text to match the series format.

    That said, you could in fact use VBA to simplify the task of adding leader lines. I would extend the series by one point, remove the last marker and change the last line segment to a leader line kind of format (thin line, different format from the series lines) and center the label on the added point. You could do this manually, and see if you like it, if so, automate it.

  7. What about adding Application.ScreenUpdating = False at the beggining of the macro and Application.ScreenUpdating = True at the end? Not a crucial thing, but it might speed up the procedure.

  8. Stružák –

    Good point. I usually remember. In this case it probably wouldn’t make too much difference, but it’s a good habit to get into.

  9. Hello Jon,

    I have entered this into a workbook, and I am getting the series name, but I was wondering how to alter the code so that I can have the series name and the value. Any help would be greatly appreciated!

    Thanks!

  10. LEM –

    You need to change one line of code:

        ' add label
        mySrs.Points(iPts).ApplyDataLabels AutoText:=True, _
            LegendKey:=False, ShowSeriesName:=True, ShowValue:=True, _
            Separator:="" & Chr(10) & ""

    Chr(10) puts the value onto a new line, that is, separates it with a line feed (ASCII character 10). A little testing shows you can use any string you want, even multiple characters as the separator. I tried these and all did as expected:

    Separator:=", "
    Separator:=" - "
    Separator:=" ### "
  11. Thank you Jon! And I appreciate your quick response!!

  12. Hi Jon,

    I’m wondering how to rotate the data labels 270 degrees using vba, as well as format the data labels to a certain category (Accounting), with 0 decimal places.

    Even when I do this manually to a data label, I then have to move the label down so it lays within the column of data. I’m sure there is a way to do this as well, but am at a loss.

    Thanks!

  13. Barrett –

    I turned on the macro recorder while making some minor adjustments and came up with this modified procedure (note the red text). It doesn’t do the number format, since it assumes the series name is a string, but if you format the series names the way you want, it should work.

    Sub LabelLastPoint()
      Dim mySrs As Series
      Dim iPts As Long
      Dim bLabeled As Boolean
      If ActiveChart Is Nothing Then
        MsgBox "Select a chart and try again.", vbExclamation
      Else
        For Each mySrs In ActiveChart.SeriesCollection
          bLabeled = False
          With mySrs
            For iPts = .Points.count To 1 Step -1
              If bLabeled Then
                ' handle error if point isn't plotted
                On Error Resume Next
                ' remove existing label if it's not the last point
                mySrs.Points(iPts).HasDataLabel = False
                On Error GoTo 0
              Else
                ' handle error if point isn't plotted
                On Error Resume Next
                ' add label
                mySrs.Points(iPts).ApplyDataLabels _
                    ShowSeriesName:=True, _
                    ShowCategoryName:=False, ShowValue:=False, _
                    AutoText:=True, LegendKey:=False
                With mySrs.Points(iPts).DataLabel
                  .Position = xlLabelPositionCenter
                  .Orientation = xlUpward
                End With
                bLabeled = (Err.Number = 0)
                On Error GoTo 0
              End If
            Next
          End With
        Next
        ActiveChart.HasLegend = False
      End If
    End Sub
     
  14. Hi Jon. Just found some interesting behaviour using the Label Last Point macro. I’ve got a dynamic series set up to display x number of years of a time series. When I set x to 10 years and run the macro, I get this:
    http://screencast.com/t/MWIzYjYzM

    When I change x to 20 years, I get this:
    http://screencast.com/t/OTcxZDYw

    I can see what’s going on here…by changing x then I’m effectively introducing new points into the graph after the originally labelled points. So I guess I could either make the macro re-run on change of x, or maybe position the data labels to the far right of the graph, albeit with the same verticle position of the data points.

    Any thoughts on these approaches?

    On an related note, I’m displaying the last x years of the series, and instead of using the normal offset function you cover in your posts I’m using this: =OFFSET(HLFS!$A$1,,MATCH(LastCell,HLFS!$1:$1)-1,1,-year_input-1)

    …where lastcell is the named formula =9.99999999999999E+307 (i.e. I’m using the bignum approach).

    This works a treat if you always want your series to finish at the last entry of the row. The usual COUNTA arguement instead of MATCH wouldn’t work in my specific case, because some series have several blanks in the rows, which would through the offset off by the number of blank cells.

    You ever do a blog post on bignum?

    Cheers

    Jeff

  15. I just realised that positioning the data labels to the far right of the graph won’t work unless you use some VBA to adjust their vertical height in relation to the last datapoint shown for whatever x is selected.

  16. Jeff –

    That’s the way data labels work. A label is always at the ith point, so if you remove j points at the beginning of the series, the label sticks to what is now the ith point, but which was the (i+j)th point.

    If you need the label to stick to a given XY value in a dynamic setting, you should use a hidden series with points to anchor labels (one custom XY point per label). To keep the last point labeled in a dynamic setting, use a dynamic pair of XY values for that point’s label. No need for VBA.

  17. THanks Jon, that’s a great work-around.

  18. Hi, I have a dynamic chart using your Label Last Point, is there any way to put the values units after the value, as you would do with format – number – custom.

    Any Help would be appreciated

    Ian

  19. Ian –

    If the cell containing the value is formatted using a custom number format that shows the units, the label will also show the units.

  20. Hi Jon,

    I discoverd a ‘feature’ in Excel 2010 when labeling series and combining charts, which wasn’t there in Excel 2002. After a lot of searching the www, I still have not found the solution.

    The following occurs in 2010:

    When creating 2 different charts on a worksheet, and giving some points of the series in each chart one or more data labels, the labels of the copied to chart will disappear when copying one chart in to the other one. (in 2002 the labels did not disappear) (both scatter plots)

    (i have 20 charts, with more than 10 series each, which i all want to combine into 1 single chart, its a kind of overview of objects postioned on a surface, so no lines)

    Is there a simple work around or do i have to write some VBA code to bypass this new ‘feature’…

  21. I haven’t used 2010 enough to know about this problem. I suspect that it isn’t new to 2010, though, because it reminds me of some 2007 frustrations.

  22. Hi Jon… I have a question related somewhat to this post and couldn’t find a solution.. hoping you can help please!

    I’ve created a ‘concentric’ donut chart in Excel 2003 and have placed labels next to the outermost donut – the labels are dynamic. The user completes a form, there is some sorting of data and the updated donut appears fine. The issue is that the labels are roughly in the same area (and near the correct donut segment) but have to be adjusted manually each time.. the donut is always the same size and the same number of segments.. is there any way I can get the data labels to stay in exactly the same place each time?

    Have tried everything I can think of including padding the data labels with the same number of characters but they still appear in different locations each time… I would appreciate any suggestions you might have please!!

    Thanks

  23. Hi Jon,

    I am also stuck with the same issue as “Coach” from 2010 that is, doughnut charts with label positions set to be “outer end” do not automatically position themselves next to the middle of the respective doughnut section.

    As i am automating some charts being sent to Powerpoint i really want to solve this without user interaction.

    I realised that PIE charts do automatically set labels positioned “outer end” in the correct place, would it be possible to change chart type to Pie, record position of the label and change back to Doughnut then set the position as that recorded?

    I think yes but i do not know how to record the position of the label in VBA.

    moving a label with macro recorder on gives me below but i dont think the numbers are properties as such, any ideas?

    I look forward to your comments
    Neil

    ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
    Selection.Left = 116.873
    Selection.Top = 7.875

  24. Once you reposition a label, it is no longer tied to its position relative to its data point. In some cases, Excel seems to keep it in the right relative position. In a donut chart, though, it’s not X and Y that change, it’s the angle around the circumference of the chart.

    Without getting into the merits of the donut chart type (see Leave the Donuts for the Cops, and Stick with the Bars if you’re wondering what I would say), I can give an idea of how to better position the labels.

    You can find the center of the circle because it’s the same as the center of the plot area. Reapply data labels to the last series, which centers them in each arc. determine the distance between each label’s .top and .left properties and the X and Y of the center of the circle. Multiply these differences by a reasonable factor, then add them back to the center X and Y.

    This might get you started:

    Sub RepositionDonutLabels()
      Dim xCenter As Double, yCenter As Double
      Dim xLabel As Double, yLabel As Double
      Dim iLabel As Long
      Const rFactor As Double = 1.3
      
      With ActiveChart
      
        With .PlotArea
          xCenter = .InsideLeft + .InsideWidth / 2
          yCenter = .InsideTop + .InsideHeight / 2
        End With
        
        With .SeriesCollection(.SeriesCollection.Count)
          .DataLabels.Delete
          .ApplyDataLabels xlDataLabelsShowLabel
          For iLabel = 1 To .Points.Count
            With .Points(iLabel).DataLabel
              xLabel = .Left - xCenter
              yLabel = .Top - yCenter
              .Left = xCenter + xLabel * rFactor
              .Top = yCenter + yLabel * rFactor
            End With
          Next
        End With
        
      End With
      
    End Sub
  25. Hi Jon,

    Thanks for your response. I actually think the solution is much simpler than that because changing the chart to Pie i can see the labels are in the correct position and i can then simply change to a doughnut having recorded the position of the label’s “Top” and “Left” values and reapply the position. I even wrote a not very efficient procedure to do just that ( i have not got my head round Arrays just yet).

    Problem ‘was’ that the code allowed me to step through it but when i ran from beginning it would stop at the first assignment of a Label Top or left value.

    Now it wont even let me step through it.

    Any comments are welcome:
    Many Thanks, Neil

    Option Explicit

    Sub DoughnutLabels()

    Dim TopSource(1 To 4) As Double
    Dim LeftSource(1 To 4) As Double
    Dim DoughnutChart As Chart
    Dim Sers As SeriesCollection

    Set DoughnutChart = Worksheets(“PowerpointTableTemplates”).ChartObjects(“DoughnutChart”).Chart
    Set Sers = DoughnutChart.SeriesCollection

    ‘remove and re apply labels
    With Sers(1)

    .ChartType = xlPie
    .HasDataLabels = False
    .HasDataLabels = True
    .DataLabels.Position = xlLabelPositionOutsideEnd
    .DataLabels.ShowValue = True
    .DataLabels.ShowCategoryName = True

    End With

    ‘Set position of labels
    TopSource(1) = Sers(1).DataLabels(1).Top
    LeftSource(1) = Sers(1).DataLabels(1).Left
    TopSource(2) = Sers(1).DataLabels(2).Top
    LeftSource(2) = Sers(1).DataLabels(2).Left
    TopSource(3) = Sers(1).DataLabels(3).Top
    LeftSource(3) = Sers(1).DataLabels(3).Left
    TopSource(4) = Sers(1).DataLabels(4).Top
    LeftSource(4) = Sers(1).DataLabels(4).Left

    ‘Change to Doughnut
    DoughnutChart.SeriesCollection(1).ChartType = xlDoughnut

    With DoughnutChart.SeriesCollection(1)

    .DataLabels(1).Top = TopSource(1)
    .DataLabels(1).Left = LeftSource(1)
    .DataLabels(2).Top = TopSource(2)
    .DataLabels(2).Left = LeftSource(2)
    .DataLabels(3).Top = TopSource(3)
    .DataLabels(3).Left = LeftSource(3)
    .DataLabels(4).Top = TopSource(4)
    .DataLabels(4).Left = LeftSource(4)
    .DataLabels.Font.Size = 6.5
    End With

    End Sub

  26. i forgot to include the Error which is “method top of object datalabel failed”

  27. Hmm, my method uses only a dozen or so lines of code, accommodates a variable number of data labels, does not require changing the chart type, and has no errors to debug. I’d say it’s pretty simple.

  28. My solution is simpler to understand for amateur VBA people such as myself, thats what i meant by ‘simple’.

  29. Things should be as simple as possible, no simpler.
    Neil, in my humble opinion, your solution is simple. Jon’s is elegant. AND simple.

    Amature VBA people might learn some valuabale lessons from Jon’s code, such as writing simple code that doesn’t have things hard-coded. Your code only works on a chart called DoughnutChart sitting in a worksheet called PowerpointTableTemplates with 4 data series.

    While your code works fine under these limits, Jon’s handles any, and is more visually appealing to boot…his code centres the labels perfectly right smack in the middle of each series.

    Your statement “My solution is simpler to understand for amateur VBA people such as myself, thats what i meant by ‘simple’.” I interpret as My solution is simpler [for me] to understand , thats what i meant by ‘simple’. “

  30. It’s been my experience that in an xy scatter chart

    With .Points(iLabel).DataLabel
    .Top = somenumber

    can cause a r/t error in excel 2007 unless the datalabel is first .Selected

    Has this ever happened to you? The errors I get are the .Top method fails or else overflow. Sometimes going into Debug at the error and pressing F5 continues anyway. Simply .Selecting the datalabel first seems to eliminate the problem.

    Brian Murphy

  31. Brian –

    Yeah, so much of 2007’s chart system is undercooked.

    I’ve had lots of issues with labeling in 2007. Some of the issues are avoided by inserting DoEvents here and there. Selecting labels makes them somewhat more reliable, and I think there are other tricks. Unfortunately, most measures that improve reliability also make your code run excruciatingly slowly. I’ve even had to insert a progress bar dialog so the user sees that the code is still running.

  32. I’m also finding that excel 2010 does not have this problem with datalabel positions! In fact, excel 2010 runs my chart intensive macros way faster than excel 2007. Not as fast as excel 2003, but close. Excel 2010 does choke badly, though, when putting data point symbols on just 10 or 20 points of a series with a few thousand points (i.e. sparse symbols).

    Brian

  33. Quick question. Do you know how to change the label color to match the series. I had a similar macro that did this in Excel 2003 but now I can’t get it to work in 2010. I’m doing this:

    [code]series.DataLabels.Font.ColorIndex = .MarkerForegroundColor[/code]

  34. Try
    series.DataLabels.Font.Color = series.MarkerForegroundColor

  35. I’ve tried a bunch of methods and nothing seems to work. I think the problem is that the series colors are all set to automatic. If I debug print I get the following for all series in the chart:
    Series.MarkerForegroundColor = -1
    Series.MarkerForegroundColorIndex = 2

    I’m guessing I need to either set the series colors manually and then do the same for the labels, or somehow apply theme colors sequentially in the same order as excel’s automatic chart colors. The latter is what I don’t know how to do. My code is below. It labels the end point of each series with the series name in place of a legend.

    If Not ActiveChart Is Nothing Then
    For Each mySrs In ActiveChart.SeriesCollection
    With mySrs
    nPts = .Points.count
    .Points(nPts).ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
    .Points(nPts).DataLabel.Text = mySrs.Name
    .DataLabels.Font.Bold = True
    .DataLabels.Font.Color = .MarkerForegroundColor
    End With
    Next
    End If

  36. In older Excel versions, the .Color property would give you the color, and .ColorIndex would say whether it was automatic or not. It’s not so easy to get the actual color in new versions.

    You could try to figure out what color is used for which series in the chart. But if you’ve deleted series and added now ones, the added ones fill in the formats of the deleted ones. Also, once there are more than the half dozen palette colors in use, the shading is auto-adjusted, so your carefully determined colors are no longer quite the same as in the chart.

  37. Thanks for that. I think the simplest solution is just to set all the colors manually. My solution was to make a case-select theme function that returns a long representing 5 preset RGB colors (this particular app only has a max 5 series). Then I can do:

    i = 1
    If Not ActiveChart Is Nothing Then
    For Each mySrs In ActiveChart.SeriesCollection
    themeColor = GetThemeColor(i)
    With mySrs
    .MarkerForegroundColor = themeColor
    .MarkerBackgroundColor = themeColor
    .format.Line.ForeColor.RGB = themeColor
    .format.Line.BackColor.RGB = themeColor
    .MarkerStyle = 5
    .MarkerSize = 6
    .DataLabels.Font.color = themeColor
    End With
    i = i + 1
    Next
    End If

    Thanks for your help!

  38. Hi, Jon.
    Thanks a lot for your macro code. Now i’m trying to use this macro for my chart. However, I find the problem because my some of my last points is #N/A and it show no label in my chart. How can i add label to other point that had value instead?
    Thanks.

    Chonlada

  39. Chonlada –
    I had an algorithm built into the code here that detected missing points in Excel 2007 and earlier, because an error occurred when you tried adding a label to a nonexistent point.
    Excel 2010 lets you add a label to a nonexistent point, though I guess you can’t see the label, because it contains no text. I’ve corrected the code here so that it also detects a label with no characters, then continues to the next point.
    I don’t know which way 2013 handles labels for missing points, probably the way that 2010 does. But this labeling code seems to work fine in 2013, so I presume I’ve handled whichever way it labels missing points.

  40. Thanks Jon.
    It definitely works. U are the best. :)

  41. This is a very helpful post; thanks!

    Question: Is it possible from VBA to customize font attributes of characters in a data label across different points in a series? This works manually: I can select a data label and color some characters red. I record a macro with those steps, and when I run the macro, it doesn’t work–all data labels update whenever one data label’s format is updated.

    I have several dozen charts, each with a few series to update, so doing this work manually is not an option. I’m an experienced VBA programmer and originally tried updating the Points(i).DataLabel.Characters(Start, Length).Font.Color property, but when I step through the macro, I see all data labels updating with each run through the loop. I’ve also tried Points(i).DataLabel.Format.TextFrame2.TextRange.Characters(Start, Length).Font.Fill.ForeColor.RGB, but that yields the same unhappy result.

    I’d appreciate any help–I’ve been banging my head against this one for a few weeks!

  42. Based on a couple recorded macros, I assembled this code to change the font color of the third character of the specified datalabels to a greenish color:

    Sub FormatDataLabelCharacters()
      Dim iPt As Long, nPts As Long
      With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        nPts = .Points.Count
        For iPt = 1 To nPts
          With .Points(iPt).DataLabel.Format.TextFrame2.TextRange.Characters(3, 1).Font
            .Fill.ForeColor.RGB = RGB(0, 255, 63)
          End With
        Next
      End With
    End Sub

    I discovered that if the data label consists of text that’s defined elsewhere, such as series name, category label, Y value, or a formula link to a cell, the entire data label changes. If the data label is plain old text (e.g., you typed it yourself), then just the specified character is changed.

    After running the code a few times, the Characters(3,1) line started giving me errors. I don’t know what changed. But I made a slight adjustment that converts each label to plain text before applying the format:

    Sub ConvertAndFormatDataLabelCharacters()
      Dim iPt As Long, nPts As Long
      With ActiveSheet.ChartObjects(2).Chart.SeriesCollection(1)
        nPts = .Points.Count
        For iPt = 1 To nPts
          With .Points(iPt).DataLabel
            .Text = .Text
            With .Format.TextFrame2.TextRange.Characters(3, 1).Font
              .Fill.ForeColor.RGB = RGB(0, 255, 63)
            End With
          End With
        Next
      End With
    End Sub

    No matter where each label originated from, only the third character is formatted.

  43. Thanks for the quick research and quick reply; I’m very impressed and grateful for the help!

    I’d actually like to color different characters in different data labels. For example, if I change this line of the code:

    With .Format.TextFrame2.TextRange.Characters(3, 1).Font

    to this:

    With .Format.TextFrame2.TextRange.Characters(iPt, 1).Font

    I’d like to see the first character green on the first data label, the second character green on the second data label, etc. But when I run the code, I instead see all data labels have the same characters colored green. Excel can’t seem to vary color formats across data labels in the series–or can you find a way? Thanks again!

  44. I made that exact change to my code, and the first label had the first character formatted, the second label had the second character formatted, etc.

  45. When I step through the code, the data label for point one updates fine, then formatting applied to data label 2 also applies to data label 1, and so on. After running all the code, only the data label for the last point is formatted correctly; all earlier data labels have the cumulative formatting of all other data labels.

    Are you running in Excel 2012 / 2013? I’m running in Excel 2010; perhaps that explains the difference.

    Other than upgrading Excel (which is not imminent at my company), the only workaround I see is to create textboxes on the chart object. Textboxes aren’t linked to points (or to each other), so there is more formatting freedom–but then it becomes more difficult to update the textboxes later once the data values change.

    Thanks again for your help; I appreciate it!

  46. Dennis –
    As a matter of fact, I’ve been using Excel 2013, and the labels are well behaved.
    I just fired up 2010, and the code formats all labels at once, not just the indicated label.
    I had thought that Excel 2010’s charting infrastructure was (mostly) fixed, compared to 2007’s, but here’s one thing they missed.
    If you don’t have too many labels, you could add an extra XY series for each point, plotted over the relevant point, without line or marker, but with individually formatted data label. Actually, if it’s done via code, “not too many” labels is quite a few.

  47. Hi,

    I have a simple data label macro (for an xy scatterplot) from the MS support website. This worked great in Excel 2003, but does not work in Excel 2010.

    I get a run time error (Object variable or With block variable not set).

    Any guidance on resolving this by adjusting the code?

    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

  48. Nadeem –
    Which line of code was highlighted when you got the error?

  49. This is a great procedure but it only works on one cluster of a clustered bar chart. I have racked my brain on how to make it work, and I can’t figure it out. Maybe it can’t be done. To see what I mean, see this screen shot. The procedure only labels the bars on the rightmost cluster of the clustered bar chart and will not label the others.

  50. Nelson –

    What this routine does is label only the last point in a series, to avoid clutter and also speed up the process. It’s really more effective in a line chart. In a clustered column chart, the last point in each series is in the last cluster. If you want to label all of the columns, add labels to each entire series.

  51. Prateek kothari says:

    Hi Jon,
    Is it posssible to bring data label in between .
    For ex
    i am showing data for 12 months of temperature (line chart weekly) at four places say india us australia and china.
    So is it possible to show data label for say week 5 only or week 6 only preferably using excel

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0