Format all data labels at once


Board Regular
Dec 9, 2013
Is there really no way to do this? I am trying to create a 'parallel coordinates'-type viz, which is actually a line graph. So I have a set of terms, with two values each: one showing how important that concept is in one corpus (a set of documents), the other how important it is in another corpus. The idea is to compare that in a visual way. This obviously works, but I can't find a way to format all data labels all together. Instead I have to do them one by one by rotating through them with the Tab-key. Also, I do not seem to be able to find a way to let Excel do this in an visually optimized way - so the labels are all over the place, as you can see here. So is there really no way - through a built-in function OR through a script - to get this job done automagically?

BTW - Here are the data
Nuclear weaponsCold warConventional deterrenceSouth AsiaSoviet UnionNuclear warStrategic stabilityCyber deterrenceInternational relationsNuclear disarmamementArms controlInternational securityNorth KoreaMissile defensesNuclear arms control
Deterrence-IS, Russia/English (Scopus)10087460643281032806162635556
Deterrence-IS (Scopus)1007951494847474747472923291815

Excited whether you grandmasters will be able to figure this one out as well!

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
We could use VBA to loop through the data labels, which would be much less tedious than manually looping through them.

What formatting do you want to apply?
Upvote 0
Wow - the famous Jon Peltier himself! Thanks for getting back to me Jon!

I'm not sure exactly what you mean by formatting here. But so ideally I guess what would work best is if
  • the 'left' data labels would be to the left of the left data marks; and the 'right' ones to the right of the right data marks
  • they'd also be right-aligned on the left, and left-aligned on the right, so that they'd all look nice and flush
  • I like the leaders, especially because they allow to deal with values that have multiple labels (so in this case like value 47 on the right). In cases like that, it would be great if these different labels could be evenly distributed vertically (and come to think of maybe, maybe I'd be better off doing this more detailed formatting in powerpoint?)
  • the actual font size etc. are things I could take care of I guess...
Is that what you were asking for? Thanks again for looking into this!
Upvote 0
These are the things I assumed you meant, but I'm glad you specified.

I'm on my phone right now, so no VB Editor. I'll try to shoot off a quick reply in the morning.
Upvote 0
My code is below. Select a chart and run it.

I have assumed a slope chart with two points per series, any number of series. It removes a legend, if present, adds data labels to each series showing series name and value, ensures data labels are one line only (no word wrap within a label), colors the labels to match the series line, and positions the labels to the left of the left point and to the right of the right point. It does not handle the overlapping points, which makes it much more complex, so you need to move them manually (for a fee I would attempt this because it would take hours). The leader lines only appear after you move the labels out of their default left or right position.

Let me know if you have any questions or if I've left out anything.

VBA Code:
Sub ApplySlopeChartDataLabels()
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation
    GoTo ExitSub
    With ActiveChart
      .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
    End With
  End If
End Sub
Upvote 0
Fantastic. Thanks much! And I'm sure that blog entry will once again be useful to far more people. Please do keep up the good work...
Upvote 0

Forum statistics

Latest member

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
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 "".
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