Monday, November 18, 2013

slopegraph template

I've found myself increasingly using slopegraphs as of late. They can be useful when you have two time periods of data and want to quickly see increases/decreases between the two periods (example below; see second half of this post for more discussion and another example).
From a formatting standpoint, however, they are annoying. They take a lot of time to set up because basically everything is different from graphing application defaults. I realized as I was making a recent one that I make the exact same changes every single time and may actually leverage a template for this (I say "may actually" because I thought that would be the case once before, but it didn't happen, though I've heard from others that they do use it).

In case you find yourself wanting to use a slopegraph (or quickly see whether one will work given the specifics of your data), you can download the Excel template I created here (screenshot below).


  1. I think one of the strengths of this graph is the double label ... without this graph loses much of its value. Here:
    is our approach with a dynamic solution

  2. Here is some VBA code I use to change an ordinary graph into a slope chart (with inspiration from Jon Peltier's website quite some time ago). This is part of a form, so readers can adjust the cboChartNames.Value and chkSlopeLbl.Value to the appropriate values.

    Private Sub Convert_To_Slope()
    If cboChartNames.Value = vbNullString Then Exit Sub

    Dim i%
    With ActiveSheet.ChartObjects(cboChartNames.Value).Chart
    ' Can't do with more than 2 points in a series
    ' Manually swap series and points and try again.
    If .SeriesCollection(1).Points.Count <> 2 Then Exit Sub
    ' Plot by rows
    .PlotBy = xlRows
    ' Hide vertical axis tick marks and labels
    .Axes(xlValue, xlPrimary).Border.LineStyle = xlNone
    .Axes(xlValue, xlPrimary).MajorTickMark = xlNone
    .Axes(xlValue, xlPrimary).MinorTickMark = xlNone
    .Axes(xlValue, xlPrimary).TickLabelPosition = xlNone
    ' Set horizontal axis position to on tick marks
    .Axes(xlCategory, xlPrimary).AxisBetweenCategories = False
    ' Turn the legend off
    .HasLegend = False
    ' Format the series lines to be thinner
    For i% = 1 To .SeriesCollection.Count
    .SeriesCollection(i%).Format.Line.Weight = 0.5
    ' .border.weight also works
    ' Set the data labels - will make both sides visible
    .SeriesCollection(i%).HasDataLabels = True
    ' .Points(i%).DataLabel.ShowValue = false ' will turn off label on one side
    ' We want to put the range name on the selected point
    If chkSlopeLbl.Value = True Then
    .SeriesCollection(i%).Points(1).DataLabel.ShowSeriesName = True
    .SeriesCollection(i%).Points(2).DataLabel.ShowSeriesName = True
    End If
    ' Format the data label color to be the same as the line
    ' and format the label position on the left to be on the left
    .SeriesCollection(i%).Points(1).DataLabel.Font.Color = .SeriesCollection(i%).Format.Line.ForeColor
    .SeriesCollection(i%).Points(1).DataLabel.Font.Size = 8
    .SeriesCollection(i%).Points(1).DataLabel.Position = xlLabelPositionLeft
    .SeriesCollection(i%).Points(2).DataLabel.Font.Color = .SeriesCollection(i%).Format.Line.ForeColor
    .SeriesCollection(i%).Points(2).DataLabel.Font.Size = 8
    .SeriesCollection(i%).Points(2).DataLabel.Position = xlLabelPositionRight
    Next i%

    End With
    End Sub