Avoiding overlapping labels in charts

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Hi guys,

I have created this book with a ton of formulas and macros within it which boils down to a line chart to compare sales of 2 objects over months dynamically. My problem is that if the sales figures for the two objects in the same month are too close to each other after entering new data, the labels I have set for the line charts overlap. Is there a solution for this (be it with VBA or without)?

Thanks for your help!
 

Attachments

  • Overlapping Data Labels.PNG
    Overlapping Data Labels.PNG
    173.2 KB · Views: 9

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
Hi Allen. You can place and adjust your data labels with VBA as below. You can probably also do it manually without VBA as well. I'm guessing the position is the only relevant thing but I've included some other stuff for U to mess with. HTH. Dave
Code:
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).DataLabels
.position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With

With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(2).DataLabels
.position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 5 'blue
End With
Adjust sheet name, chart object number and series number to suit
 

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Hi Allen. You can place and adjust your data labels with VBA as below. You can probably also do it manually without VBA as well. I'm guessing the position is the only relevant thing but I've included some other stuff for U to mess with. HTH. Dave
Code:
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).DataLabels
.position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With

With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(2).DataLabels
.position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 5 'blue
End With
Adjust sheet name, chart object number and series number to suit

Hi ND. Good thinking with label position above and below but I tried that earlier and there are two problems with it as you can see in the new image I uploaded. The series can still overlap given the suitable situation. And when I use label position below it overlaps the x axis in most of the cases. I'm guessing I need a code with an if statement or something.

Thank you for your help though! It might well help some other people on the forum.
 

Attachments

  • Overlapping Data Labels 2.PNG
    Overlapping Data Labels 2.PNG
    165.3 KB · Views: 3

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723

ADVERTISEMENT

I "borrowed" and adapted some code from Jon Peltier. You can give this a trial. Seems to work. Dave
Code:
Function GetYValue(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.Values
  GetYValue = vCats(iPtNum)
End Function

Function GetYLabel(iSrs As Long, ipt As Long) As Double
  Dim s As String, cht As Chart
  Set cht = ActiveSheet.ChartObjects(1).Chart
  s = GetYValue(cht, iSrs, ipt)
GetYLabel = s
End Function

Private Sub Test()
Dim cnt As Long
For cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, cnt) >= GetYLabel(2, cnt) Then

With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
Else
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
End If
Call Test to operate. Adjust sheet name, chart name and series numbers to suit
 

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
I "borrowed" and adapted some code from Jon Peltier. You can give this a trial. Seems to work. Dave
Code:
Function GetYValue(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.Values
  GetYValue = vCats(iPtNum)
End Function

Function GetYLabel(iSrs As Long, ipt As Long) As Double
  Dim s As String, cht As Chart
  Set cht = ActiveSheet.ChartObjects(1).Chart
  s = GetYValue(cht, iSrs, ipt)
GetYLabel = s
End Function

Private Sub Test()
Dim cnt As Long
For cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, cnt) >= GetYLabel(2, cnt) Then

With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
Else
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
End If
Call Test to operate. Adjust sheet name, chart name and series numbers to suit
Hi Dave!

First of all I'd like to thank you for your determination to solve this, much appreciated!

The code sort of worked (I think) at for some labels and not for the others. Please see the new image. In some cases labels in both lines turned to red in some cases none. But there still is overlapping. I am attaching my version of the code maybe you could spot what I am doing wrong?

All I have done so far is writing in the sheet name, chart name, series number (1 and 2), put a next at the end and write in end sub at the end. Please have a look see at the code.

All the best and happy holidays!

VBA Code:
Function GetYValue(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.Values
  GetYValue = vCats(iPtNum)
End Function

Function GetYLabel(iSrs As Long, ipt As Long) As Double
  Dim s As String, cht As Chart
  Set cht = ActiveSheet.ChartObjects(1).Chart
  s = GetYValue(cht, iSrs, ipt)
GetYLabel = s
End Function

Private Sub Test()
Dim cnt As Long
For cnt = 1 To Sheets("Expense Analysis Dashboard").ChartObjects("Chart 38").Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, cnt) >= GetYLabel(2, cnt) Then

With Sheets("Expense Analysis Dashboard").ChartObjects("Chart 38").Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
Else
With Sheets("Expense Analysis Dashboard").ChartObjects("Chart 38").Chart.SeriesCollection(2).Points(cnt).DataLabel
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
End If

Next

End Sub
 

Attachments

  • Overlapping Data Labels 3.PNG
    Overlapping Data Labels 3.PNG
    250.3 KB · Views: 1

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
Hmmm.... for some reason the whole code didn't post and I didn't notice? Dave
Code:
Sub test()
Dim cnt As Long
For cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, cnt) >= GetYLabel(2, cnt) Then

With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
Else
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(cnt).DataLabel
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 3 'red
End With
End If
Next cnt

For cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(2).Points.Count
If GetYLabel(2, cnt) > GetYLabel(1, cnt) Then
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(2).Points(cnt).DataLabel
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 5 'blue
End With
Else
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(2).Points(cnt).DataLabel
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 5 '5 blue
End With
End If
Next cnt
End Sub
edit: again make changes to sheet name, chart name & series to suit. Also change the color to your series color. You also continue to need the 2 functions...
Code:
Function GetYValue(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.Values
  GetYValue = vCats(iPtNum)
End Function

Function GetYLabel(iSrs As Long, ipt As Long) As Double
  Dim s As String, cht As Chart
  Set cht = ActiveSheet.ChartObjects(1).Chart
  s = GetYValue(cht, iSrs, ipt)
GetYLabel = s
End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,230
Messages
5,600,424
Members
414,383
Latest member
kevinlarey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top