Format all data labels at once

[sdspieg]

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 weapons	Cold war	Conventional deterrence	South Asia	Soviet Union	Nuclear war	Strategic stability	Cyber deterrence	International relations	Nuclear disarmamement	Arms control	International security	North Korea	Missile defenses	Nuclear arms control
Deterrence-IS, Russia/English (Scopus)	100	87	46	0	64	32	81	0	32	80	61	62	63	55	56
Deterrence-IS (Scopus)	100	79	51	49	48	47	47	47	47	47	29	23	29	18	15

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

 

[Jon Peltier]

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?

 

[sdspieg]

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!

 

[Jon Peltier]

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.

 

[sdspieg]

Sure. That would be fantastic. Thanks much!

 

[Jon Peltier]

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.

Sub ApplySlopeChartDataLabels()
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation
    GoTo ExitSub
  Else
    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
      Next
    End With
  End If
ExitSub:
End Sub

 

[Jon Peltier]

This was a great question, and I wanted to share my VBA labeling routine more broadly, so I've written it up in my blog. See Slope Chart with Data Labels.

 

[sdspieg]

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