Overlapping Data Labels
Data labels are terribly tedious to apply to slope charts, since these labels have to be positioned to the left of the first point and to the right of the last point of each series. This means the labels have to be tediously selected one by one, even to apply “standard” alignments.
I recently wrote a post called Slope Chart with Data Labels which provided a simple VBA procedure to add data labels to a slope chart; the procedure simplified the problem caused by positioning each data label individually for each point in the chart.
The problem is that often points are located close to each other; the result: overlapping data labels. I showed a feature in my commercial software that lets you fine-tune the position of individual data labels. But even with that helpful tool, it is still a tedious process.
A more inclusive VBA procedure that would take care of overlapping data labels is a much more difficult task. I thought about it for a while. Since each set of labels is aligned horizontally with the points they describe, I would only have to worry about vertical positioning. I would have to sort the labels by vertical position, then see if a label overlapped any subsequent label. Also, I should allow some overlap, since labels include a small white margin around their text. But it seemed like a lot of work.
My friend and colleague Andy Pope came to the rescue. He sent an email with a nifty VBA routine he’d worked out. I remembered that long ago Andy had a prototype label untangler, and I know I played with it. But what he sent me now was much better than a prototype. I tested it and liked it, then I thought about how I would build my own.
Andy’s routine used a class module to create a DataLabel object for each label that had to be realigned. His routine inserts each DataLabel object into a VBA Collection in such a way that the Collection is sorted by vertical position. Then the code loops through the labels: if two were to overlap, the upper label is moved up a tiny bit and the lower one down a little bit. This must be done iteratively because moving two labels apart might move either one closer to a third label. After looping until all labels were separated by a certain amount, the code finishes.
I decided to build a slightly different routine, because I already have a slope chart feature in my commercial software (which will be released in a new build soon), and I’d like something that worked with it. To compile all the labels, the program builds a two-column VBA array, with series numbers in the first column and vertical position in the second. The code bubble-sorts this array by the second column. Then it loops through the series numbers in a nested loop, to compare each label with every other label.
The VBA Routines
My entire module is shown below. I declare a couple of constants used in the positioning. When I move labels I do so by a pixel at a time, which in Windows is specified by MoveIncrement = 0.75 (0.75 points, to be exact). If I want to run this on a Mac, I must change MoveIncrement to 1, because this is how the Mac measures screen distances (one of the myriad incompatibilities we have to deal with). After a few trials, I set an OverlapTolerance of 0.45, meaning I would let labels overlap by 45% of their height.
The routine that does the work is FixTheseLabels. It first loops through the series of the chart, and if the series has a valid label on the point being tested (we text point 1 for the left side labels and point 2 for the right), then the series number and the top position of the label are stored in the array. If the series has no valid data label, then zeros are stored in the array.
When the array is populated, the code calls BubbleSortArrayByColumn (a routine I cobbled together years ago) to sort by the top position of the labels. After sorting the labels, the label top position in the second column of the array is no longer needed.
The code loops through the array, getting the series number from the first column; if it reads a zero, it means there isn’t a valid label, so it skips to the next array row. The program gets one label from the first loop and one from the second. If the top of the second label is less than OverlapTolerance (0.45 or 45%) higher than the bottom position of the first, the first label moves up and the second moves down.
Two lines have been inserted before the ExitSub label in the ApplySlopeChartDataLabels that call the new FixTheseLabels procedure to eliminate overlapping.
The entire code module is shown below. Here is a list of the procedures and what they do.
ApplySlopeChartDataLabelsToActiveChart
Apply Data Labels to Active Chart, and Correct Overlaps
Can be called using Alt+F8ApplySlopeChartDataLabelsInActiveSheet
Apply Data Labels to Charts on Active Sheet, and Correct Overlaps
Can be called using Alt+F8ApplySlopeChartDataLabelsInActiveWorkbook
Apply Data Labels to Charts on Active Sheet, and Correct Overlaps
Can be called using Alt+F8ApplySlopeChartDataLabelsToChart(cht As Chart)Apply Data Labels to Chartcht
Called by other code, e.g.,ApplySlopeChartDataLabelsToActiveChartFixTheseLabels(cht As Chart, iPoint As Long, LabelPosition As XlDataLabelPosition)
Correct Overlaps in Data Labels of Chartcht
Called by ProcedureApplySlopeChartDataLabelsToChartBubbleSortArrayByColumn(MyArray As Variant, iSortCol As Long)
Sorts List of Data Labels by Vertical Position
Called by ProcedureFixTheseLabels
The first three procedures are helper procedures which the user can run to process the active chart, all charts on the active sheet, or all charts on all worksheets in the active workbook. They can easily be accessed with the shortcut Alt+F8 or by clicking the Macros button on the Developer tab, to open the Macro dialog.
Start of Entire Code Module
Option Explicit
Const MoveIncrement As Double = 0.75 ' 0.75 points = 1 pixel
Const OverlapTolerance As Double = 0.45
Const Overflow As String = "-nan(ind)"
Sub ApplySlopeChartDataLabelsToActiveChart()
ApplySlopeChartDataLabelsToChart ActiveChart
End Sub
Sub ApplySlopeChartDataLabelsInActiveSheet()
ApplySlopeChartDataLabelsInSheet ActiveSheet
End Sub
Sub ApplySlopeChartDataLabelsInActiveWorkbook()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ApplySlopeChartDataLabelsInSheet ws
Next
End Sub
Sub ApplySlopeChartDataLabelsInSheet(ws As Worksheet)
Dim chob As ChartObject
For Each chob In ws.ChartObjects
ApplySlopeChartDataLabelsToChart chob.Chart
Next
End Sub
Sub ApplySlopeChartDataLabelsToChart(cht As Chart)
With cht
.HasLegend = False
Dim iSeries As Long
For iSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(iSeries)
Dim iColor As Long
iColor = .Format.Line.ForeColor.RGB
.HasDataLabels = True
.HasLeaderLines = True
With .DataLabels
.ShowValue = True
.ShowSeriesName = True
.Font.Color = iColor
.Format.TextFrame2.WordWrap = False
With .Item(1)
.Position = xlLabelPositionLeft
End With
With .Item(2)
.Position = xlLabelPositionRight
End With
End With
End With
Next
End With
FixTheseLabels cht, 1, xlLabelPositionLeft
FixTheseLabels cht, 2, xlLabelPositionRight
ExitSub:
End Sub
Sub FixTheseLabels(cht As Chart, iPoint As Long, LabelPosition As XlDataLabelPosition)
Dim nLabels As Long
nLabels = cht.SeriesCollection.Count
Dim vDataLabels As Variant
ReDim vDataLabels(1 To nLabels, 1 To 2)
Dim iLabel As Long
For iLabel = 1 To nLabels
Dim srs As Series
Set srs = cht.SeriesCollection(iLabel)
If srs.Points(iPoint).HasDataLabel Then
Dim dlbl As DataLabel
Set dlbl = srs.Points(iPoint).DataLabel
If dlbl.Position <> LabelPosition Then
dlbl.Position = LabelPosition
DoEvents
DoEvents
End If
If CStr(dlbl.Height) <> Overflow Then
vDataLabels(iLabel, 1) = iLabel
vDataLabels(iLabel, 2) = dlbl.Top
Else
vDataLabels(iLabel, 1) = 0
vDataLabels(iLabel, 2) = 0
End If
Else
vDataLabels(iLabel, 1) = 0
vDataLabels(iLabel, 2) = 0
End If
Next
BubbleSortArrayByColumn vDataLabels, 2
Do
Dim DidNotOverlap As Boolean
DidNotOverlap = True
Dim FirstIndex As Long, SecondIndex As Long
For FirstIndex = 1 To nLabels - 1
If vDataLabels(FirstIndex, 1) > 0 Then
Dim FirstLabel As DataLabel
Set FirstLabel = cht.SeriesCollection(vDataLabels(FirstIndex, 1)). _
DataLabels(iPoint)
For SecondIndex = FirstIndex + 1 To nLabels
If vDataLabels(SecondIndex, 1) > 0 Then
Dim SecondLabel As DataLabel
Set SecondLabel = cht.SeriesCollection(vDataLabels(SecondIndex, 1)). _
DataLabels(iPoint)
If FirstLabel.Top + FirstLabel.Height * (1 - OverlapTolerance) > _
SecondLabel.Top Then
DidNotOverlap = False
FirstLabel.Top = FirstLabel.Top - MoveIncrement
SecondLabel.Top = SecondLabel.Top + MoveIncrement
End If
End If
Next
End If
Next
If DidNotOverlap Then Exit Do
Dim LoopCounter As Long
LoopCounter = LoopCounter + 1
If LoopCounter > 30 * nLabels Then Exit Do
Loop
End Sub
Sub BubbleSortArrayByColumn(MyArray As Variant, iSortCol As Long)
Dim FirstItem As Long, LastItem As Long
FirstItem = LBound(MyArray, 1)
LastItem = UBound(MyArray, 1)
Dim LastSwap As Long
LastSwap = LastItem
Do
Dim LoopCounter As Long
LoopCounter = 1 + LoopCounter
If LoopCounter > 10000 Then Exit Do
Dim IndexLimit As Long
IndexLimit = LastSwap - 1
LastSwap = 0
Dim iRow As Long
For iRow = FirstItem To IndexLimit
If (MyArray(iRow, iSortCol) > MyArray(iRow + 1, iSortCol)) Then
' if the items are not in order, swap them
Dim jCol As Long
For jCol = LBound(MyArray, 2) To UBound(MyArray, 2)
Dim TempValue As Variant
TempValue = MyArray(iRow, jCol)
MyArray(iRow, jCol) = MyArray(iRow + 1, jCol)
MyArray(iRow + 1, jCol) = TempValue
Next
LastSwap = iRow
End If
Next
Loop While LastSwap
End SubEnd of Entire Code Module
The Result
Here is the chart with overlapping data labels, before running FixTheseLabels. Half of the labels are illegible.
Here is the chart after running the routine, without allowing any overlap between labels (OverlapTolerance = zero). All labels can be read, but the space between them is greater than needed (you could almost stick another label between any two adjacent labels here), and some labels have moved far from the points they label.
Finally, here is the chart after running the routine with OverlapTolerance = 0.45. Labels are closer together, but not too close, and some labels did not have to be moved (for example, see the lowest three labels on the right side of the chart). I tried a series of values for the overlap tolerance: zero tolerance obviously led to excessive spacing, 50% led to labels being a little too close, 40% was a little too far apart and some labels were moved that shouldn’t have to be. 45% seems to be the Goldilocks setting, though this probably varies with the font used for the labels.
Bob Lalonde says
Hi Jon
How did you know I needed this really cool solution?
This will get me out of a jam for sure,
Thank you very much,
Bob
Jon Peltier says
Bob –
Glad to help!
Jag says
Is it possible to use this code for only labels on one side of the data? What about if not every series is labeled (e.g. line chart with one area plot in the background where the area does not need to be labeled)?
Stephan says
Jon, thanks for adding this. But I’m not quite sure what exactly we have to do. You clearly say we should add those two lines in the first VBA module you made. But so what do we do with the rest of the code? Does that also go in there? Are those to become separate modules? I’m sure that’s obvious to many, but not to all (including me). Thanks!
Stephan says
Can anybody please help?
Jon Peltier says
Stephan –
I replied to your comment (or thought I had, but I don’t see it here) after I adjusted the VBA code I’d posted. This time I’ve included the labeling routine from the previous post with the two lines that call the code that corrects overlapping.
Miky says
Hi, Jon! This is one of the most useful module I’ve ever seen, congrats!
It works amazing…but, I have a tricky question for you all. What if I have multiple separate charts in the same Excel sheet?
I should think a macro to look through the sheet and select one by one the charts (and activate them) and the apply your routine to them (one by one).
For the “look through” macro I found this one:
Sub LoopThroughCharts()
‘PURPOSE: Loop through every graph in the active workbook
‘SOURCE: http://www.TheSpreadsheetGuru.com/The-Code-Vault
Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Application.ScreenUpdating = False
Application.EnableEvents = False
Set CurrentSheet = ActiveSheet
For Each sht In ActiveWorkbook.Worksheets
For Each cht In sht.ChartObjects
cht.Activate
‘Do something with the chart…
Next cht
Next sht
CurrentSheet.Activate
Application.EnableEvents = True
End Sub
…now my question is: where to put this module in your module?
thank a lot
Jon Peltier says
Hi Miky –
My entire code module is below. I have changed the
ApplySlopeChartDataLabelsprocedure toApplySlopeChartDataLabelsToChartso that it works on any given chart. I’ve addedApplySlopeChartDataLabelsInSheetwhich will do all charts on any given worksheet. Then there are three routines that handle the following choices. Note that while this compiles, I haven’t really tested all of this.Process active chart
ApplySlopeChartDataLabelsToActiveChartProcess all charts on active sheet
ApplySlopeChartDataLabelsInActiveSheetProcess all charts on all sheets in active workbook
ApplySlopeChartDataLabelsInActiveWorkbookI’ve updated the entire code module in the article above.
Iryna says
Hi, Jon!
Thanks for adding this module, it is really useful.
But I have a question. When I ran the module on one of the charts, I got an error (Error ‘6’ – Overflow). My guess would be that the problem is with the data, since one of the data series has no data for one of the time series; when I excluded this data series, the VBA script worked. So does all the data series have to have data for the VBA macro not to be thrown off?
Thanks a lot!
Iryna says
Could anyone please help with this one?
Also this is the file with data in case it will help https://docs.google.com/spreadsheets/d/14NPDL2Sflp2N43aezUNLHpLu1zocA6_g/edit?usp=sharing&ouid=116207885029269031077&rtpof=true&sd=true
Jon Peltier says
Hi Iryna –
Two things:
1. “N/A” is not recognized by Excel as N/A, it is simply text, and Excel plots it as a zero. You need to use #N/A or =NA(). This makes Excel treat the missing data as a blank. But in most cases, a blank cell should work out fine.
2. The code references the size and position of the data labels. But a missing data label (from a blank cell or a #N/A value) has a wacky value for any size or position, i.e.,
-nan(ind), which is such a huge negative number that it causes the overflow error. I thought I had caught it usingIf IsNumeric(dlbl.Height) Thenbut as it happens,
-nan(ind)is numeric. So what I have done is incorporate a constant:Const Overflow As String = "-nan(find)"and changed my test to
If CStr(dlbl.Height) <> Overflow ThenI’ve updated the entire code module in the article above.
Jose Luis Lancha Garcia says
Hi Jon!
Great piece of work! I am trying to implement it with a chart that has lots of labels, long text in them. I seem to be able to make it work, the only weird thing I get is that all labels get the dlbl.height as -nan(ind)… Not sure if I am missing anything to be declared… My label values are coming from a range of cells, just in case it helps.
Jon Peltier says
Jag –
Sorry, missed your questions earlier.
The way the overlap-correcting code works is you tell it which point to process via
iPointinSub FixTheseLabels(cht As Chart, iPoint As Long, LabelPosition As XlDataLabelPosition)Then this line skips any points without a label:
If srs.Points(iPoint).HasDataLabel ThenThomas Tessmer says
Hi – I only have basic VBA knowledge (enough for me but nothing like people of your caliber). Could you point me to where your code defines what is included in the labels and how they are formatted. When I use your code, it adds elements to the label, makes some of them centered (I want them all to be to positioned to the right), and changes the text color from black to white.
Any time you have to respond is appreciated.
Jon Peltier says
Thomas –
The labels are defined for a slope chart, from the previous post. Settings for a slope chart’s labels may not be applicable to a more general-purpose chart.
iColor = .Format.Line.ForeColor.RGBdetermines what color the series line is, and
.Font.Color = iColorapplies that color to the label text.
.ShowValue = True.ShowSeriesName = Truemake the labels show the Y value and series name of the labeled series
.Position = xlLabelPositionLeft.Position = xlLabelPositionRightposition the label either to the left or the right
Thomas Tessmer says
Thanks so much Jon. Sorry that I overlooked that this was specific to a slope chart. I will see if I can get it to work for my application. You are appreciated.
oswald says
If I want to apply it to a line chart and color mark only the last bank series and avoid overlapping, how should I modify the program？
Jon Peltier says
If you mean you only want labels on the last point of each series in a line chart (and of course, all series have the same number of points), try this:
oswald says
Thank you very much for your reply, but I have not been able to run this program successfully, where does it need to be inserted to run together？
I would like to add the expression of avoiding repetition at the last point of the line chart´･ᴗ･`
Jon Peltier says
See my tutorial How To: Use Someone Else’s Macro.
Eledah says
Is there a way to prevent overlap in all kinds of charts? I’m talking about the data labels in scatter charts, line charts etc.
Jon Peltier says
Hi Eledah –
One could conceivably write routines to prevent overlapping of data labels in a general type of chart in two dimensions, though we should probably use the word “reduce” rather than “prevent”. Overlapping data labels becomes more of a problem the more points and labels there are and the longer the labels may be, adding horizontal positions adds complexity, as does the possibility of using different data label positions (judicious use of left/right can unoverlap some labels without fine repositioning). Also, care must be taken to keep labels close enough to their data points without obscuring them.
An internet search of “excel vba overlap data labels” will find you many attempts to solve the problem, with various levels of success. I’ve implemented a few different approaches in various projects, which work within the constraints of each project but are not generally useful. My approach in this article fits this description.
CZa says
Amazing Jon!
How should i modify the the code to run “FixTheseLabels” on all points and all series?
Jon Peltier says
When I was fixing the labels in the slope chart, I was fixing points 1 and 2 in all series. My code looks like this:
To extend it to all points in a chart, I might use something like this:
In a line chart, all series use the same X values, so point 1 for all series is at the first X value, point 2 for all series is at the second X value, etc. This prevents my algorithm from being extended to XY charts with arbitrary, unsorted, and different X values for each series. If you have numerous points, the labels may overlap horizontally, and my routine ignores that issue as well.
I can’t imagine trying to handle the complexity of overlapping data labels in XY charts.
Natalie Forde-leaves says
Hi Jon, I know the above comment says you cant imagine handing XY charts but if there is any update on this i really need it :) i have a scatterplot/bubble chart and can have say 4 different labels that all refer to one position on a bubble chart e.g. say X=10, Y=20 can have 4 different text labels (e.g. short quotes). The labels are text and are selected from a range of cells using ‘value from cells’. I have to manually move all the labels into a space that i create by having a bigger chart area than the plot area – if you have any ideas on how to automate this in VBA i would be super grateful!
Jon Peltier says
Hi Natalie –
Sorry, but I have not proceeded to the general X-Y case of overlapping labels. In a line chart, points line up vertically, so I only have to account for vertical overlap. In an X-Y chart, points can be located anywhere along either axis in any order, so any label could overlap any other label. It’s an enormous task just to determine which labels overlap, never mind moving them without overlapping other labels.
NF says
Hi Jon, I am trying to implement this code for a horizontal bar chart which seems to have too many smaller sections within that cause their labels to overlap. How do I change this code for a horizontal bar graph? And what individual module sections need to be changed to make this adjustment?
Jon Peltier says
Seems easy, right? Prevent data labels from overlapping. But it’s very complicated and the approaches differ greatly from case to case.
I tackled a small piece of the problem, labels in line or column charts. These labels are horizontally aligned and horizontally oriented, so I only had to deal with small vertical displacements to correct small overlaps.
In a bar chart, the labels are vertically aligned and horizontally oriented. The overlaps will be larger, and labels may have to be moved horizontally or vertically to resolve this. It may be possible to address this case with adjustments to my routine, but I’d have to see the chart with its labels to know.
Aurélie says
Hi Jon,
thanks for providing the code for not having overlapping labels.
In my case, it works fine when running the macro the first time; I’m working with a ‘dynamic’ chart, and as soon I change the data, the labels format stay the one as ran the first time, so it happens again that labels are overlapping each other.
Is there a way to have the format of the labels refresh each time new data are displayed?
I’m not sure i was able to well explain my issue.
Thank you in advance for your help.
Aurélie
Jon Peltier says
Hi Aurélie –
That code is static, in that it only runs once when you invoke it.
It’s possible to trigger the code to run when your data changes. You would need to set up a Worksheet_Change or Worksheet_Calculate event procedure in the code module related to the changing data’s parent worksheet. This event procedure would detect a change, determine whether the change occurred in the chart’s source data, and if so, it would then call the routine to fix the labels.
I have a few tutorials that might help get you started:
Dynamic Chart Source Data
Click a Worksheet Cell to Change a Chart
Link Excel Chart Axis Scale to Values in Cells
Maneesh says
Hi Jon. Thank you for this out of the world code! Is there a way to tweak this for bubble charts or scatter plots?
Aurélie Acoca says
Hi Jon,
thank you for your answer about adding a ‘worksheet_change’.
I did try before seeing your answer, it didn’t work. I hope the links you provided might propose something different that will work.
I will definitely come back on this page again as I’m really interested to see, as Maneesh, if there is any code we could use to help when labelling scatter plots (especially when using a third column for the labels, the ‘Value from cells’ option). It’s often so difficult to read the labels on those charts if we don’t manually reposition them.
Thank you again for your help.
Aurélie
Jon Peltier says
If values in cells change because formulas have recalculated, then Worksheet_Change will not capture the changes. You can use Worksheet_Calculate instead.
Alex Zar says
Thanks Jon, your code is amazing!
Is there any workaround or change in the code in case you need to avoid horizontal overlaps.
Thank you in advance for your answer!
Jon Peltier says
Horizontal overlaps will probably be harder than vertical overlaps, since labels are larger in the horizontal direction. (And of course, arbitrary overlaps of labels that are not aligned horizontally as with the slope chart labels in this article are horrifically difficult.)
In my code I compare each label’s top plus height (that is, its bottom) to the next label’s top, and if it overlaps by more than a tolerance factor, then it is deemed to overlap and the two labels are moved away from each other.
To avoid horizontal overlapping, you need to compare each label’s left plus width to the next label’s left to determine whether an overlap is present. Technically it is no harder than vertical overlapping, except for the shape of the labels.
Steven says
Just a small correction to your workbook sub there:
“ApplySlopeChartDataLabelsToSheet” should be “ApplySlopeChartDataLabelsInSheet”
Jon Peltier says
Steven –
Thanks, fixed it.
Jeppe Lykou says
Hi John
Great module, makes my life much easier, but I’ve hit a bump on a “new” type of chart for me.
I would have thought that MS had a simple “Do not stack labels” option – They do not, and this of cause is not all bad, because it forces me to learn something :)
My question concerns datalabels within the chart instead of on either sides.
I’ve created at Stepgraph and want to show the series name and new values with datalabels when one of the series changes. This task was not too difficult. However, if two or more of the series changes at the same X-value to the same Y-value, these datalabels will stack on eachother, making them unable to read.
Hope the above makes sense – I’d like to post a picture along with it if it was possible.
I’ve tried messing around in your code to make overlapping datalabels stack on top of each other – but I must acknowledge that my skill level is incufficient. Do you have a solution for this? or can you point me in a direction, where i might find one?
Best regards
Jeppe Lykou
sakina says
I havea scatterplot with lots of data points. Its an XY chart. I tried to run this modeule but it gives me this error “Ambigous name detetcted: Apply SlopeChartDataLabelsinActiveSheet
Jon Peltier says
1. Make sure you don’t have two procedures with the same name. Check in other modules too.
2. This particular program was designed for line charts with labels aligned vertically on specific points. It is not designed for scatter charts, which would require much more complex algorithms.
James Trimble says
Hi Jon, I’ve been working on an algorithm for this problem that should run very fast. I’ve written a VBA implementation at https://gist.github.com/jtrim-ons/9a672c163c8bf03ab744e794249da691 . I’d love to hear your thoughts if you manage to find time to take a look (and obviously, no problem if you don’t!)
Jon Peltier says
Hi James –
You questioned these lines in my code:
If vDataLabels(FirstIndex, 1) > 0 ThenIf vDataLabels(SecondIndex, 1) > 0 Then
The reason for these lines is to ignore points without labels. When my code encounters such a point, the label positions cause an overflow, and the code assigns zeros to the respective array elements. When the code later encounters these zeros, it ignores the point when determining label positions.
Your trick of subtracting heights does not take into account these missing labels, and missing labels will crash your routine, but that’s easy to fix using if you incorporate the above lines where appropriate.
I haven’t figured out yet exactly what subtracting heights is supposed to do, but I think you could apply and remove it without touching the labels: apply it to the original label positions, recalculate the new label positions, remove it from the label positions, then apply the new positions to the labels.
I corrected your code, then ran it side by side against mine for a simple chart. The results are visibly identical, with moved labels within less than a pixel between your algorithm and mine. Close inspection shows your previously overlapping labels are closer than mine, probably because I impose an arbitrary move increment and you do not.
In the simple chart, my algorithm takes 0.15 to 0.20 seconds while yours takes 0.30 to 0.40 seconds (range of 3 trials).
Original chart:
Jon’s Label Positioning:
Jim’s Label Positioning:
James Trimble says
Hi Jon, thanks so much for trying out my code, and for your very useful comments.
I’ve fixed my code to handle missing labels correctly. I’ve also done the subtraction without moving labels, as you suggested, which speed up the code quite a bit on the chart I tried it on.
The PAVA approach tends to be most useful when there are many data points that are close together. Using the example data from your blog post with 15 series, I get run times of around 0.9 seconds using your code and 0.05 to 0.06 seconds using mine.
James Trimble says
Now that I think about it, I bet that the slow part of both algorithms is asking Excel to actually move the labels. If that’s true, your algorithm could also perhaps run much faster if the calculations were performed on an array of positions with the final positions only written to the labels as a last step.
Wendy Alexander says
Hi Jon,
I am a novice when it comes to VBA and macros. I read through all the above messages and this sounds amazing if I could get it to work for my line charts. I see that different charts were discussed but I did not see line charts (maybe I missed it). Will this work for line charts? I have several on one sheet for different trends that I do and I am constantly having to move the labels around to better position them, especially the ones that have more than on axis line. I simply have a running trend for each of my locations. I finally learned how to get my charts to update with the last 6 weeks, but if I could have it adjust my labels….that would make my life!! :)
Jon Peltier says
Hi Wendy –
This approach works for line charts: the slope charts I designed it for are line charts. It is designed for charts that have labels aligned vertically (and perhaps overlapping) at particular horizontal positions. This requirement removes a lot of the constraints for a generalized label-overlap-removal tool.
Jean-Seb says
Jon,
Fantastic piece of code!!!! really appreciate the sharing here.
One question: the code is attached to changing labels for each series of a graph containing a “single data point” each. How would you change the code if you had only one series with multiple points (such as a bubble point graph)?
Thanks in advance
Jon Peltier says
Hi Jean-Seb –
My code only looks at labels for one point of a line-chart type series: the X position of each label is the same, so it only has to consider the Y positions of the labels. This greatly simplifies the process.
Trying to control both X and Y positions is a much more difficult algorithm to write. You would have to consider overlapping rectangles and moving labels both horizontally and vertically to make them fit. I get a headache just thinking about it.