Label Each Series in a Chart
by Jon Peltier
Wednesday, October 15th, 2008
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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
' 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
bLabeled = (Err.Number = 0)
On Error GoTo 0
End If
Next
End With
Next
ActiveChart.HasLegend = False
End If
End Sub
To implement this procedure, follow the steps in How To: Use Someone Else’s Macro.
Related Posts:
- Label Last Point for Excel 2007
- Connect Two XY Series with Arrows (VBA)
- Order of Points in XY and Line Charts
- Connect Two XY Series with Arrows – 2007 Error
- Format All Series The Same
- VBA to Split Data Range into Multiple Chart Series
Posted: Wednesday, October 15th, 2008 under VBA.
Comments: 33
Comments
Comment from Darlene
Time: Thursday, October 16, 2008, 3:49 pm
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
Comment from Jon Peltier
Time: Thursday, October 16, 2008, 4:04 pm
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.
Comment from Jaanus
Time: Tuesday, October 21, 2008, 8:19 am
I would then add
ActiveChart.HasLegend = False
to the very end to remove the now redundant chart legend.
Comment from Jon Peltier
Time: Tuesday, October 21, 2008, 3:09 pm
Jaanus – Good idea. I missed it because I probably allready deleted the legend, but I’ve added it to the code above.
Comment from Petr
Time: Wednesday, October 22, 2008, 5:06 am
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
Comment from Jon Peltier
Time: Wednesday, October 22, 2008, 7:45 am
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.
Comment from Stružák
Time: Friday, October 31, 2008, 5:44 pm
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.
Comment from Jon Peltier
Time: Friday, October 31, 2008, 8:07 pm
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.
Comment from LEM
Time: Thursday, January 8, 2009, 11:02 am
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!
Comment from Jon Peltier
Time: Thursday, January 8, 2009, 11:56 am
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:=" ### "
Comment from LEM
Time: Thursday, January 8, 2009, 1:14 pm
Thank you Jon! And I appreciate your quick response!!
Comment from Barrett
Time: Tuesday, June 16, 2009, 12:28 pm
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!
Comment from Jon Peltier
Time: Tuesday, June 16, 2009, 4:04 pm
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
Comment from Jeff Weir
Time: Thursday, February 11, 2010, 4:04 am
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
Comment from Jeff Weir
Time: Thursday, February 11, 2010, 4:07 am
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.
Comment from Jon Peltier
Time: Thursday, February 11, 2010, 6:59 am
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.
Comment from Jeff Weir
Time: Thursday, February 11, 2010, 1:10 pm
THanks Jon, that’s a great work-around.
Comment from Ian
Time: Wednesday, November 24, 2010, 4:14 pm
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
Comment from Jon Peltier
Time: Wednesday, November 24, 2010, 5:54 pm
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.
Comment from JvdB
Time: Wednesday, December 8, 2010, 2:48 am
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’…
Comment from Jon Peltier
Time: Wednesday, December 8, 2010, 9:38 am
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.
Comment from Coach
Time: Tuesday, December 21, 2010, 3:19 am
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
Pingback from Anonymous
Time: Friday, September 9, 2011, 3:18 am
[...] [...]
Comment from neil clarke
Time: Tuesday, March 6, 2012, 11:59 am
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
Comment from Jon Peltier
Time: Tuesday, March 6, 2012, 2:33 pm
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
Comment from neil clarke
Time: Monday, March 12, 2012, 8:15 am
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
Comment from neil clarke
Time: Monday, March 12, 2012, 8:18 am
i forgot to include the Error which is “method top of object datalabel failed”
Comment from Jon Peltier
Time: Monday, March 12, 2012, 10:09 pm
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.
Comment from neil clarke
Time: Tuesday, March 13, 2012, 4:57 am
My solution is simpler to understand for amateur VBA people such as myself, thats what i meant by ‘simple’.
Comment from Jeff Weir
Time: Tuesday, March 13, 2012, 3:37 pm
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’. “
Comment from Brian Murphy
Time: Tuesday, March 27, 2012, 6:39 am
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
Comment from Jon Peltier
Time: Tuesday, March 27, 2012, 8:29 am
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.
Comment from Brian Murphy
Time: Tuesday, March 27, 2012, 8:46 am
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
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.