Excel macro to correct matching data labels in a line chart

Question

I am looking for / trying to make a macro to fix the position of data labels in a line chart with one or more collections of series so that they do not overlap.

I thought about some ways of my macro, but when I try to do this, I understand that it is too difficult for me, and I have a headache.

Is there something I missed? Do you know about such a macro?

Here is an example chart with overlapping data labels:

Here is an example diagram where I manually fixed the data labels:

Answer 1

This task basically breaks down into two stages: accessing the Chart object to get Labels and manipulating the positions of labels to avoid a match. p>

For this sample, all rows are displayed on the common X axis, and the X values ​​are common enough that the labels do not overlap in this dimension. Therefore, the solution offers only deals with label groups for each point X in turn.

Shortcut access

This Sub parses the chart and creates an array of Labels for each point X, in turn,

 Sub MoveLabels() Dim sh As Worksheet Dim ch As Chart Dim sers As SeriesCollection Dim ser As Series Dim i As Long, pt As Long Dim dLabels() As DataLabel Set sh = ActiveSheet Set ch = sh.ChartObjects("Chart 1").Chart Set sers = ch.SeriesCollection ReDim dLabels(1 To sers.Count) For pt = 1 To sers(1).Points.Count For i = 1 To sers.Count Set dLabels(i) = sers(i).Points(pt).DataLabel Next AdjustLabels dLabels ' This Sub is to deal with the overlaps Next End Sub 

Overlap detection

This calls AdjustLables with an array of Labels . These labels must be checked for overlap.

 Sub AdjustLabels(ByRef v() As DataLabel) Dim i As Long, j As Long For i = LBound(v) To UBound(v) - 1 For j = LBound(v) + 1 To UBound(v) If v(i).Left <= v(j).Left Then If v(i).Top <= v(j).Top Then If (v(j).Top - v(i).Top) < v(i).Height _ And (v(j).Left - v(i).Left) < v(i).Width Then ' Overlap! End If Else If (v(i).Top - v(j).Top) < v(j).Height _ And (v(j).Left - v(i).Left) < v(i).Width Then ' Overlap! End If End If Else If v(i).Top <= v(j).Top Then If (v(j).Top - v(i).Top) < v(i).Height _ And (v(i).Left - v(j).Left) < v(j).Width Then ' Overlap! End If Else If (v(i).Top - v(j).Top) < v(j).Height _ And (v(i).Left - v(j).Left) < v(j).Width Then ' Overlap! End If End If End If Next j, i End Sub 

Moving shortcuts

When an overlap is detected, you need a strategy that moves one or both labels without creating another overlap.
There are many possibilities, you did not provide enough details to judge your requirements.

Note about Excel

For this approach to work, you need an Excel version with the DataLabel.Width and DataLabel.Height properties. Version 2003 SP2 (and, presumably, earlier) does not.

Answer 2

This macro will prevent overlapping labels on two line charts if the data source is listed in two adjacent columns.

 Attribute VB_Name = "DataLabel_Location" Option Explicit Sub DataLabel_Location() ' ' ' *******move data label above or below line graph depending or other line graphs in same chart*********** Dim Start As Integer, ColStart As String, ColStart1 As String Dim RowStart As Integer, Num As Integer, x As Integer, Cell As Integer, RowEnd As Integer Dim Chart As String, Value1 As Single, String1 As String Dim Mycolumn As Integer Dim Ans As String Dim ChartNum As Integer Ans = MsgBox("Was first data point selected?", vbYesNo) Select Case Ans Case vbNo MsgBox "Select first data pt then restart macro." Exit Sub End Select On Error Resume Next ChartNum = InputBox("Please enter Chart #") Chart = "Chart " & ChartNum ActiveSheet.Select ActiveCell.Select RowStart = Selection.row ColStart = Selection.Column ColStart1 = ColStart + 1 ColStart = ColNumToLet(Selection.Column) RowEnd = ActiveCell.End(xlDown).row ColStart1 = ColNumToLet(ActiveCell.Offset(0, 1).Column) Num = RowEnd - RowStart + 1 With ThisWorkbook.ActiveSheet.Select ActiveSheet.ChartObjects(Chart).Activate ActiveChart.SeriesCollection(1).ApplyDataLabels ActiveChart.SeriesCollection(2).ApplyDataLabels End With For x = 1 To Num Value1 = Range(ColStart & RowStart).Value String1 = Range(ColStart1 & RowStart).Value If Value1 = 0 Then ActiveSheet.ChartObjects(Chart).Activate ActiveChart.SeriesCollection(1).DataLabels(x).Select Selection.Delete End If If String1 = 0 Then ActiveSheet.ChartObjects(Chart).Activate ActiveChart.SeriesCollection(2).DataLabels(x).Select Selection.Delete End If If Value1 <= String1 Then ActiveSheet.ChartObjects("Chart").Activate ActiveChart.SeriesCollection(1).DataLabels(x).Select Selection.Position = xlLabelPositionBelow ActiveChart.SeriesCollection(2).DataLabels(x).Select Selection.Position = xlLabelPositionAbove Else ActiveSheet.ChartObjects("Chart").Activate ActiveChart.SeriesCollection(1).DataLabels(x).Select Selection.Position = xlLabelPositionAbove ActiveChart.SeriesCollection(2).DataLabels(x).Select Selection.Position = xlLabelPositionBelow End If RowStart = RowStart + 1 Next x End Sub ' ' convert column # to column letters ' Function ColNumToLet(Mycolumn As Integer) As String If Mycolumn > 26 Then ColNumToLet = Chr(Int((Mycolumn - 1) / 26) + 64) & Chr(((Mycolumn - 1) Mod 26) + 65) Else ColNumToLet = Chr(Mycolumn + 64) End If End Function 

Answer 3

Although I agree that regular Excel formulas cannot fix everything, I don't like VBA. There are several reasons for this, but the most important is that it will stop working with the next update. I'm not saying that you should not use VBA at all, but use it only when necessary.

Your question is a good example of necessity when VBA is not required. "Well, you say," but how can I fix this problem? "Feel good luck and click this link to answer the corresponding question here .

What you will learn in the link, how you can measure the exact grid of your diagrams. When the x axis crosses 0, you only need the maximum label of the Y axis for this. Now you are only halfway, because your specific problem has not yet been resolved. Here's how I will continue:

First, measure how tall your marks compare with the height of the chart. This will require some trial and error, but should not be very difficult. If your chart can add 20 labels without overlapping, this number will be, for example, 0.05.

Next, determine whether and where any of the labels will overlap. This is pretty simple because all you have to do is figure out where the numbers are too close to each other (within the range of 0.05 in my example).

Use some logic tests or whatever I need to get the IF formulas. As a result, you get a table with answers for each series (except the first). Do not be afraid to repeat this table again for the next step: creating a new chart entry.

There are several ways to create a new chart, but here is the one I would choose. For each series, create three lines. The first is a real line, the other two are invisible lines with data labels. For each line there is one invisible line with only regular labels. All use the same alignment. Each additional invisible line has a different binding for shortcuts. You will not need one for your first series, but for the second the label will be on the right, the third on the bottom, and the fourth on the left (for example).

If none of the data labels overlap, only the first invisible lines (with regular alignment) should show the values. When the marks overlap, the corresponding additional invisible line should take over this point and show its mark. Of course, the first invisible line should not show it there.

When all four labels overlap with the same x-axis value, you should see the first basic invisible line label and the three labels above the invisible lines. This should work for your sample diagram, because there is enough space to go to the labels on the left and right. Personally, I adhere only to the minimum and maximum mark at the overlap point, because its coincidence shows that the values ​​are very close to each other in the first place.

Hope this helps you

Hi,

Patrick

Answer 4

@chris neilsen Could you test your solution in Excel 2007? When I drop the objects into the DataLabel class, it looks like the .Width property has been removed from the class. (Sorry, I was not allowed to comment on your answer)

Maybe one thing to add from the forum below is to temporarily adjust the position of the label: http://www.ozgrid.com/forum/showthread.php?t=90439 "you get a close value for the width or height of the data label, displacing the label from the chart and comparing the indicated value on the left / top with the size in chartarea inside the width / height. "

Based on this, move v (i) .Width and v (j). Width to sng_vi_Width and sng_vj_Width variables and add these lines

 With v(i) sngOriginalLeft = .Left .Left = .Parent.Parent.Parent.Parent.ChartArea.Width sng_vi_Width = .Parent.Parent.Parent.Parent.ChartArea.Width - .Left .Left = sngOriginalLeft End With With v(j) sngOriginalLeft = .Left .Left = .Parent.Parent.Parent.Parent.ChartArea.Width sng_vj_Width = .Parent.Parent.Parent.Parent.ChartArea.Width - .Left .Left = sngOriginalLeft End With