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).
I think one of the strengths of this graph is the double label ... without this graph loses much of its value. Here:
ReplyDeletehttps://sites.google.com/site/e90e50fx/home/Edward-Tufte-slopegraphs-in-excel
is our approach with a dynamic solution
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.
ReplyDeletePrivate 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
Else
.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