This brief tutorial shows how to construct a slope chart in Excel, provides a simple VBA procedure to apply and format data labels quickly, and shows a finished chart with some manual repositioning of overlapping labels.
In Format all data labels at once on the Mr Excel forum, a user was frustrated with having to format data labels on his slope chart one point at a time, which is a very tedious and frustrating experience. One solution to such tedious tasks is VBA, and I wrote a procedure that would apply and format all data labels at once. The procedure ran instantly, compared to the many minutes it would take to create and format the labels by hand.
A slope chart is a nice way to compare two sets of matching data, for example, before and after sales data. The before points are plotted in the left side of a line chart, and the after points are plotted on the right, and you can trace the performance of each item by tracking the sloped lines connecting the points.
Edward Tufte introduced Slope Graphs in The Visual Display of Quantitative Information; examples of slope charts and tutorials showing how to create them in Excel abound on the interwebs.
Creating the Chart
Slope charts are not specifically built into Excel, but line charts are, so slope charts are easy to do. The complication is with data labels.
This is the slope chart data in the Mr Excel post:
Select the data and insert a line chart to create the slope chart.
Excel always tries to minimize the number of series and maximize the number of points per series, which is usually the way you want your chart to be plotted. But a slope chart has multiple series and only two points per series, so you need to switch rows and columns:
Usually the chart Excel inserts is larger than I need, but in this case, there is a lot of data and I need lots of room for my labels, so I have made the chart taller and wider.
The chart has a legend, but this legend is nearly useless. The best practice is to apply labels right on the chart. It can be done manually, but Excel first adds default labels above the points showing just the values. We need to select the labels for each series individually to add the series names, then we need to select the left and right labels separately to position them to the left of the left category or to the right of the right category. Bo-o-o-oring!
The Data Label VBA Procedure
VBA to the rescue!
The simple procedure below first removes the useless legend, which lets the actual data fill the space more effectively. Then it cycles through the series, adding data labels with leader lines to each series, specifies that the labels show series name and value, colors the label text to match the series lines, prevents wrapping of longer labels, and positions each label on the appropriate side of the chart.
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
Simply select the chart and run the code. The easiest way is to press Alt+F8, select ApplySlopeChartDataLabels, and click Run.
The chart with labels looks like this:
Repositioning the Overlapping Labels
That was fast, but there are numerous overlapping data labels. Writing VBA to alleviate this is rather complicated (I’ve tried!). You need to select each label (use two single clicks: one to select the series of labels, another to select a specific label) and drag it into position with the mouse.
I’ve taken some of the tedium out of this experience with the Move/Resize Chart Elements function in Peltier Tech Charts for Excel, my commercial charting add-in. With it, I can select a set of labels, or even an individual label, and apply a built-in position, or move it by one or more pixels or points at a time, with the arrow buttons on the dialog or with the arrow keys on my keyboard.
Using my utility it took about five minutes to disentangle all the data labels. Labels in the default left or right position do not have leader lines connecting the labels to their respective points, but once a label is moved, its leader line appears. The result looks good and is easily digested: