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.
Darlene says
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
Jon Peltier says
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.
Jaanus says
I would then add
ActiveChart.HasLegend = False
to the very end to remove the now redundant chart legend.
Jon Peltier says
Jaanus – Good idea. I missed it because I probably allready deleted the legend, but I’ve added it to the code above.
Petr says
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
Jon Peltier says
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.
Stružák says
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.
Jon Peltier says
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.
LEM says
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!
Jon Peltier says
LEM –
You need to change one line of code:
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:
LEM says
Thank you Jon! And I appreciate your quick response!!
Barrett says
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!
Jon Peltier says
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 SubJeff Weir says
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
Jeff Weir says
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.
Jon Peltier says
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.
Jeff Weir says
THanks Jon, that’s a great work-around.
Ian says
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
Jon Peltier says
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.
JvdB says
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’…
Jon Peltier says
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.
Coach says
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
neil clarke says
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
Jon Peltier says
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:
neil clarke says
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
neil clarke says
i forgot to include the Error which is “method top of object datalabel failed”
Jon Peltier says
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.
neil clarke says
My solution is simpler to understand for amateur VBA people such as myself, thats what i meant by ‘simple’.
Jeff Weir says
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’. “
Brian Murphy says
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
Jon Peltier says
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.
Brian Murphy says
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
ljo says
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]
Jon Peltier says
Try
series.DataLabels.Font.Color = series.MarkerForegroundColor
ljo says
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
Jon Peltier says
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.
ljo says
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!
Chonlada says
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
Jon Peltier says
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.
Chonlada says
Thanks Jon.
It definitely works. U are the best. :)
Dennis says
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!
Jon Peltier says
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:
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:
No matter where each label originated from, only the third character is formatted.
Dennis says
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!
Jon Peltier says
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.
Dennis says
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!
Jon Peltier says
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.
Nadeem says
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
Jon Peltier says
Nadeem –
Which line of code was highlighted when you got the error?
Nelson Nolte says
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.
Jon Peltier says
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.
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