For years I’ve been using a handy routine to label the last point of each series in a chart. Not very fancy, but it detects a point that can’t be labeled because the point itself isn’t plotted, for example, if the series values contain a blank or #N/A error. Trying to add a data label to a non-plotted point raises an error, and the program catches the error and tries the next to last point, then the point before that, until the label is added without error.
Anyway, a blog reader emailed me with a problem. The LastPointLabel procedure seemed to run fine, but her chart didn’t show the labels. Could I look at it?
I discovered another of those little inconsistencies which will keep us programmers gainfully employed for years to come. These glitches are frustrating, but I guess if I sell myself as an Excel 2007 Incompatibility Expert, I could probably double my hourly rate. They also gives me plenty to blog about.
What happens in Excel 2007 is that, even though a point is not plotted because of a blank cell or #N/A error, the label is applied without an error. You can’t see it, because the point isn’t plotted. But if you put a valid number into the cell, the point and its label appear. This is actually a nice way to handle labels for unplottable points, but the fact that the behavior is different than that of earlier versions leads to loss of functionality of existing code.
The way around this is to examine the values of the series. In 2007 and 2003, if a cell is blank then the corresponding value of the array of series data is blank. In 2007, an #N/A error also leads to a blank in the data array, whereas in 2003, an #N/A error is represented by “Error 2042”, VBA-speak for the error.
In the corrected code below, the series data is inspected before an attempt is made to label the point. It’s actually more elegant in a way, because the code doesn’t rely on a failure before it does the right thing.
Sub LastPointLabel()
Dim mySrs As Series
Dim iPts As Long
Dim vYVals As Variant
Dim vXVals As Variant
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
Application.ScreenUpdating = False
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
vYVals = .Values
vXVals = .XValues
' clear existing labels
.HasDataLabels = False
For iPts = .Points.Count To 1 Step -1
If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
' add label
mySrs.Points(iPts).ApplyDataLabels _
ShowSeriesName:=True, _
ShowCategoryName:=False, ShowValue:=False, _
AutoText:=True, LegendKey:=False
Exit For
End If
Next
End With
Next
' legend is now unnecessary
ActiveChart.HasLegend = False
Application.ScreenUpdating = True
End If
End Sub
If you’re not sure what to do with this code, please refer to my tutorial, How To Use Someone Else’s Macro.
Mike Woodhouse says
It’s just so typical of Microsoft that they should virtually hamstring themselves in an attempt to provide permanent backwards compatibility for, well, about everything, and then still fail to do so.
There’s a part of me that wishes they’d just announce from time to time that the new version of something is not guaranteed to be 100% compatible with files saved from older versions. Maybe on a less-than-strategic product (Publisher?) at first. Then we (and more importantly MS) could see how much the purchasing universe actually cares. And maybe, just maybe, we’d move forward a little faster.
In the meantime, as you rightly point out, all these shenanigans are a continuing guarantee of income to the Excel professional…
Matt Healy says
Slick code, although I tend to pick which point I want to label manually because it’s hard to automate all the relevant considerations, like trying not to obscure data or labels from another series, trying to accentuate high/low/inflection/otherwise interesting points, etc, etc.
One of my favorite tricks is to add a small data series and then label one of its points merely to draw a callout — the big advantage over manually putting a shape there is by using an added data series it will automatically get scaled and positioned appropriately.
Jon Peltier says
Matt –
This code is for relatively simple examples that are amenable to labeling of just the last point. This approach is suitable for many charts. A lot of charts of real world data become too complex for this simple approach, and you have to resort to more complicated techniques. I like the callout method you cite which uses a two-point series, I’ve used it and variations for a long time.
Jeff Weir says
Hi Jon. I’ve been trying to amend your code so that if one data label obscures another, then the one is moved up and the other moved down just a little. But I’m having no luck with this. I wondered if you might be able to steer me in the right direction.
Here’s what I’ve added to your code, after your final ‘END IF’. Pretty inelegent, I know. Very wrong too. An earlier version seemed to work on every second pass, but I’ve lost the exact syntax of that one now. Any idea where I might have gone wrong, or a more elegent way of writing this code? (note that I’ve hardcoded in the point number, which I got from the macro recorder using excel 2003).
Dim label_spacing As Long
Dim label_1_top As Long
Dim label_2_top As Long
label_1_top = ActiveSheet.ChartObjects(“Chart 1”).Chart.SeriesCollection(1).Points(29).DataLabel.Top
label_2_top = ActiveSheet.ChartObjects(“Chart 1”).Chart.SeriesCollection(2).Points(29).DataLabel.Top
label_spacing = Abs(label_1_top – label_2_top)
If label_spacing label_2_top Then
ActiveSheet.ChartObjects(“Chart 1”).Chart.SeriesCollection(1).Points(29).DataLabel.Top = label_1_top + 10 – (label_spacing / 2)
ActiveSheet.ChartObjects(“Chart 1”).Chart.SeriesCollection(2).Points(29).DataLabel.Top = label_2_top – 10 – (label_spacing / 2)
Else:
ActiveSheet.ChartObjects(“Chart 1”).Chart.SeriesCollection(1).Points(29).DataLabel.Top = label_1_top + 10 – (label_spacing / 2)
ActiveSheet.ChartObjects(“Chart 1”).Chart.SeriesCollection(2).Points(29).DataLabel.Top = label_2_top – 10 – (label_spacing / 2)
End If
End If
Jon Peltier says
Jeff –
This doesn’t seem right:
I think you want
except that label heights and widths are a pain to compute. So a constant like LABEL_HEIGHT = 10 might be reasonable. Also the dimensions are in points, so let's not use Longs.
Here's what my first-draft pseudocode would look like:
Jeff Weir says
Hi again Jon. Thanks for your reply. Not sure how I got “If label_spacing label_2_top Then “. Might have been a typo when I was troubleshooting.
When I run this, I still get the “Method ‘Top’ of object ‘Datalabel’ failed” error that I was getting before. (Apologies: I see I didn’t tell you this in my original comment).
If i hit DEBUG then the line “label_1_top = cht.SeriesCollection(1).Points(29).DataLabel.Top” is highlighted. If I then hit F5 then the code seems to work.
Note that if I go back to your unaltered ‘Label last point’ sub and sneak in the line MsgBox mySrs.Points(iPts).DataLabel.Top after the line AutoText:=True, LegendKey:=False then I get the same error, and again if i hit debug and then F5 it gives me the messagebox.
Any thougts on this?
Also, thanks for showing me how to simplify things with the Dim cht as Chart bit. I never seem to spend enough time with my head in VBA to have stuff like this crystalise.
Jon Peltier says
Jeff –
You didn’t cite a version of Excel. Sometimes 2007 thinks too slowly, and you get an error, which goes away when you stop and restart (or step through) the code. the trick here is to insert the line “DoEvents” before the line that raises the error. This tells the VBA to wait until Excel and Windows have responded to everything the code has done, like redrawn or recalculated things.
Jeff Weir says
Hi Jon. I’m working in 2007. Thanks for the tip re DOEVENTS. Unfortunately in this case it doesn’t seem to help: I tried it with both the psuedo code you posted above but no joy. I also tried testing it in the original ‘Label last point’ sub where I pasted this addition before the Exit For line (and after the mySrs.Points(iPts).ApplyDataLabels lines):
DoEvents
MsgBox mySrs.Points(iPts).DataLabel.Top
But I still get the error “Method ‘Top’ of object ‘Datalabel’ failed”
Strangely if I run the psuedo code as its own sub, it works like a charm, with the exception that the series labels no longer appear to be dynamically positioned…that is, if you change the data that the series point to, the chart labels no longer float accordingly to the new position. Which is why I was using the Label Last Point macro to remove and then re-add the labels…so that they change positon when the series change.
So maybe the datalabels created by the Label Last Point macro don’t have a .top dimension? I.e. the dimension only gets created once you move them from their default positon?
Jon Peltier says
Jeff –
The data labels do have a .Top position, as evidenced by the error going away after it stops and you hit F5.
Why don’t you send me your code (jonpeltier at gmail dot com), I’ll check it out.
Jeff Weir says
Thanks Jon, will send something through. It turns out that I can run your original ‘label last point sub’ and then run your ‘move overlapped labels’ sub seperately with exactly the desired result. But I can’t run them in one procedure, or call the ‘moved overlapped labels’ sub from the ‘label last point’ sub without getting an error.
Also, a slight amendment to what I said in my last comment: I said that if I run the code on its own, the chart labels no longer appear to move dynamically in response to a change in the chart series.
That’s not actually true…they do move; albeit they still remain ‘offset’ from the series point in question by the about they moved initially due to the code. That is, they have some kind of ‘memory’ of the amount they were moved, and they retain this bias from any subsequent changes i.e. this offset is applied from any new positons that result from a change of the data. Damned if I can work out why.
This is effectively why I wanted to run the ‘label last point’ macro first…to get rid of this bias from the memory.
Jeff Weir says
Update: I’ve found a simpler way to achieve what I wanted by not running the ‘label last points’ macro but just adding in code to delete and then insert new data labels for the points concerned.
Sub move_overlapped_data_labels()
‘this bit checks for any overlap of the labels, then moves one up and one down accordingly
Const LABEL_HEIGHT As Double = 25
Dim label_spacing As Double
Dim cht As Chart
Dim datalabel_1 As DataLabel
Dim datalabel_2 As DataLabel
Set cht = ActiveSheet.ChartObjects(“Chart 1”).Chart
cht.SeriesCollection(1).DataLabels.Delete
cht.SeriesCollection(1).Points(29).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
cht.SeriesCollection(2).DataLabels.Delete
cht.SeriesCollection(2).Points(29).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
Set datalabel_1 = cht.SeriesCollection(1).Points(29).DataLabel
Set datalabel_2 = cht.SeriesCollection(2).Points(29).DataLabel
DoEvents
label_spacing = Abs(datalabel_1.Top – datalabel_2.Top)
datalabel_1.Font.ColorIndex = 5
datalabel_2.Font.ColorIndex = 3
If label_spacing = datalabel_2.Top Then
‘MsgBox cht.SeriesCollection(1).Points(29).DataLabel.Top
datalabel_1.Top = datalabel_1.Top + (LABEL_HEIGHT – label_spacing) / 2
datalabel_2.Top = datalabel_2.Top – (LABEL_HEIGHT – label_spacing) / 2
Else
datalabel_2.Top = datalabel_2.Top + (LABEL_HEIGHT – label_spacing) / 2
datalabel_1.Top = datalabel_1.Top – (LABEL_HEIGHT – label_spacing) / 2
End If
End If
Application.ScreenUpdating = True
End Sub
Jeff Weir says
Think I found the problem, but I don’t know why it should be a problem.
In the above code I accidentally left out Application.ScreenUpdating = False at the top of the procedure, with the consequence that you can see the data labels being created and then moved.
While this looks cool, I thought I’d hide all the action from the user. But as soon as I put in Application.ScreenUpdating = False then excel bombs out with the “Method ‘Top’ of object ‘Datalabel’ failed” error.
Why the heck would that be?
Jon Peltier says
Jeff –
That’s because in Excel 2007 some chart/vba things don’t work if screenupdating is turned off. This has caused me a boatload of grief with programs that once ran smoothly and quickly, but which now are forced to run in a herky-jerk manner, much more slowly than ever before, despite faster computers.
Jeff Weir says
Hi again Jon. If I leave the screen-updating option on, then I can call the sub from another macro or I can run it directly from the code window with F5 and it works fine.
But if I call it from an event handler or run it from the macro menu (i.e. alt – F8), then I get that pesky error again despite having DOEVENTS before the offending line.
Have you had similar issues when running a sub from an eventhandler routine that otherwise works fine?
Jon Peltier says
Jeff –
When I’ve had this issue, it’s when the code is launched from a ribbon control in 2007 or using F5, or called from another routine which was launched the same way. Sometimes DoEvents has no effect.
jeff weir says
Hi Jon. Here’s a small tweak that might save a few minutes over our working lives….In the 2003 version of your labellastpoint sub I’ve added this line after the ‘applydatalabels’ bit:
mySrs.DataLabels.Font.Color = mySrs.Border.Color
…which means the text color of the data labels always matches the line color of the appropriate series.
Haven’t tested this in 2007 (only have 2003 at work).
Jeff Weir says
Hi Jon. Me again. Thought I’d share my other minor tweak to this macro, which is to add
If ActiveSheet.ChartObjects.Count = 1 Then ActiveSheet.ChartObjects(1).Select. I got tired of dismissing the dialog box after forgetting to select the only chart in the sheet!This macro has got to be my most used macro. It’s a keeper.
brooklynspo says
Thank you for the great macro.
M Bolton says
Hi Jon, great macro! With over 30 graphs that are constantly updated, the idea of manually updating the last data label for each had me near tears…
Just one thing, when I run the macro it removes the custom legend series I have in place. Is there any any way the legend can be left alone?
Thanks!
Jon Peltier says
You can delete the next to last line of code, the one that says:
cht.HasLegend = False
This line deletes the legend, so removing the line means the legend will be left intact.
M Bolton says
Thanks Jon! you’re a life-saver!
for reference, i also used ‘mySrs.DataLabels.Font.Color = mySrs.Border.Color’ on 2007 and it worked as expected.
Amber UW says
If you select each line and then click on the very end datapoint, you will get the white cross which allows you to select only the end datapoint. Then right-click and select “Format Data Label”. No macro, no fuss. :)
Jon Peltier says
Sure, no macro no fuss. And it takes fifteen seconds per series, times M series per chart, times N charts per sheet. The macro is one click for as many charts as you have selected. So maybe 15 seconds total to select the charts and click the button.
Jeff Weir says
…not to mention the fact that this macro can be automated so that it takes just seconds to process a large dashboard with hundreds of series when the file opens. Macros, no fuss.
Jeff Weir says
I’m running the Label Last Point macro on a bunch of PivotCharts, and changed the macro so that the label font adopts the same color as the underlying series. Works fine. Until that is you reopen the file, and the labels are black and white again. So DataLabel font color is NOT being saved for PivotCharts it seems.
I’m either going to convert em to regular charts, or simply write some code to cycle through all charts and adopt the series color on workbook open. Just thought I’d post this here in case someone else is having the same issue.
Jeff Weir says
Me again. I found another scenario where this code errors out. Possibly due to inconsistencies in the Object model.
I have a linechart with 4 series drawn from four different datasets, all of different length. Each dataset plots some value against some date, and I can happily mash these different-length datasets together in the one graph because I changed the Axis type to “Date Axis”, and so Excel knows “Ah, you don’t want these plotted in incremental order along that X axis, but rather you want me to treat this like an XY graph”.
But when I go to use the LabelLastPoint routine, it bombs out on the line vXVals = .XValues for the 3rd series. Strangely .XValues simply doesn’t seem to exist after series 1 and 2.
I got the code to run just by commenting out the vXVals = .XValues line and by changing this:
If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then…
…to this:
If Not IsEmpty(vYVals(iPts)) Then…
Jeff Weir says
Actually, I’m wrong about the cause of my error. The actual cause is that I was incorrect in assuming that just because you use the ‘Date Axis’ option you can throw multiple series of different lengths at a linechart.
You have to use a scatterplot for that. And indeed, if I use a ScatterPlot then the routine works just fine.
Miguel Gomez says
Hi Jon, I see I get here a few years after you wrote the code…jaja! By the way, thanks for sharing you job with us!
I was just wondering if it would be posible to copy the label format, paste it to the new one and after that erase the “old” label. If you assume it would be posible, I would start working on the code. Thanks! Miguel
Jon Peltier says
Miguel –
Sure, it’s possible. Before deleting the series’ labels, you first loop until you find the last label, then you store the label’s formatting in a few extra variables. Then you delete the labels, add the new last label, and apply the formatting.
It would go something like this:
Miguel Gomez says
Wow!
It works great! The only thing I changed was to add comments thanking you (just in case somene else takes a look at the code) and to show the value of the point rather than de Series name.
Thanks a lot!
Miguel
Nitin says
In this code every time we have to select the chart for update
How can we run this code without selecting chart ?
I mean by not considering this
If ActiveChart Is Nothing Then
MsgBox “Select a chart and try again.”, vbExclamation
Jon Peltier says
Nitin –
The code needs to know which chart to apply labels to.
If there is only one chart on the worksheet, then you can use
ActiveSheet.ChartObjects(1).Chart. Otherwise you need to use the name of the chart (by default Excel names the charts Chart 1, Chart 2, etc.):ActiveSheet.ChartObjects("Chart x").Chart. But don’t assume you know the name of the chart; versions of Excel older than 2016 or maybe 2013 sometimes did not rename a chart when it was copied, so there could be multiple charts with the same name. Hold Shift or Ctrl and select the chart: its name is shown in the Name box above cell A1. (Shift and Ctrl not required in Excel 2016 and maybe not in 2013.)Nathan says
Hi Jon
I have a dashboard that has 2 pivotcharts (Chart 4 & Chart 6) on one sheet.
I currently use the code below in “ThisWorkbook” under Microsoft Excel Objects to hide the other chart when using slicers.
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
If Target.name = “PivotTable2” Then
Worksheets(“MTD Trending”).ChartObjects(“Chart 4”).Visible = True
Worksheets(“MTD Trending”).ChartObjects(“Chart 6”).Visible = False
Else
Worksheets(“MTD Trending”).ChartObjects(“Chart 4”).Visible = False
Worksheets(“MTD Trending”).ChartObjects(“Chart 6”).Visible = True
End If
End Sub
Is there anyway to combine your code so it updates the pivot charts to show last label?
i only need it for the lines in the chart not the columns in the chart
Thanks in advance
Jenner says
I have combo chart composed of clustered bar on primary and line chart on secondary. How can I place the data labels on line chart only? I tried to use the “if” but I have no success in getting what I want.
Jon Peltier says
Jenner –
I would try something like this: