The Ultimate Guide to Slopegraphs in Excel

I’m not going to lie to you, the first time I saw a Slopegraph I was completely underwhelmed, I thought “it’s a line chart with only two points, hardly worth getting excited about”. But in recent months I have started to love Slopegraphs. By thinking of them as “just a line chart” I was seriously missing the point.

What are Slopegraphs?

Slopegraphs were first introduced by Edward Tufte back in 1983. But, I believe, it is only in recent years that this chart has been brought to the attention of the masses. Certainly, without Stephanie Evergreen and Jon Peltier, I would not be aware of Slopegraphs at all.

They work because our brains are good at comparing the slope/angle of lines. They are a great choice for displaying how values change over time. Look at the chart below – what does it tell you?

Even just a brief glace at the Slopegraph will show you that sales of Product D have improved better than all the other products, equally the sales of Product E have declined significantly. Simple, no brain power required.

But I have also found Slopegraphs useful when combining two related measures. Look at the chart below. The values on the left are the cost of an investment, and the value on the right is the value of the return. What does the chart tell you?

Even without understanding what the categories are, it is possible to see that Project G and Project F are significantly better than the other options. Project G and Project F have much higher return on investment than the other projects, so their slope is steeper. It’s not easy to compare two measures in any chart form, but it is definitely easier with a Slopegraph.

In this post, we will look at how to create Slopegraphs in Excel:

Single measure Slopegraph
Automatically highlighting a specific line
Dual measure Slopegraphs

I am using Excel 2016, but it should be possible to create a Slopegraph in any version of Excel. The location of the menus and options will differ, but the principles are the same.

Single measure Slopegraph

Let’s start with the single measure Slopegraph. This is useful for comparing two points which are equivalent to each other, but separated by time or a specific event. Good uses would be product sales over months, or company profits across two years.

The data

The data we will be using in this example is:

Within the table there are sales values for 7 products (all very imaginatively titled by me).

Create the chart

First, highlight the data in Cells A1-C8. From the menu click Insert -> Charts -> Line Chart (or Line Chart with Markers, if you want markers)

Personally, I quite like markers as they indicate where the end of the line is, but either option is fine.

The familiar line chart will appear.

Next, right click on the chart area and click Select Data….

Within the Select Data Source window there are two main boxes, in the box on the left will be the years and the products in the box on the right. We need to switch these over. Click the Switch Row/Column button, then click OK.

Now we are starting to get somewhere. We’ve not even formatted this chart and it’s already starting to tell us some key stories about Product D (yellow line) and Product E (light blue line).

Formatting the Chart

Let’s start formatting the chart. The quick and simple formatting is:

Delete the legend – click on the box which lists the products and press Delete
Write the chart title – or delete it if you don’t need it
Delete the gridlines – click on the gridlines and press Delete
Delete the left axis (radical, I know) – click on the numbers on the left axis and press Delete

The next bit of formatting is a little bit annoying. It would be great to do it all in one go, but Excel doesn’t want to work in that way.

Select a line and click Format Data Series…

From the Format Data Series menu click on the Paint Can, Line option, Solid line, then select a line color. I recommend using a mid-grey for anything which isn’t worth highlighting.

Next, click on the Marker option and select the solid fill color the same as the line color. I normally select No line for the marker, as it serves no purpose in this chart.

Now, let’s get the Data Labels sorted out. Double click on a data point on the left and select Add Data Label.

Double click on the data label which has appeared, then right click and select Format Data Label…

From the Format Data Label menu select the following

Series Name – selected
Value – selected
Separator – (space)
Label Position – Left

Add and format the Data Label on the right, in a similar way. But we do not need the Series Name, just the Value.

Repeat the formatting steps above for each line – a bit of a pain, I know. Remember to select different colors for the lines you wish to highlight.

The chart should now look like this:

Now for the final stages of formatting. The lines we want to highlight are currently behind other lines, Product D line passes under Project G line, which isn’t ideal. We can bring the highlighted colors to the front by right click on the chart and clicking Select Data. Use the up and down arrows to move the lines to be highlighted to the bottom of the list, then click OK.

Right-click on the bottom axis and select Format Axis….

From the Format Axis menu select On tick marks.

Finally, make the labels for the highlighted lines bold and the same color as the line. Then, select the chart area and size it as you need. Looks pretty good, right?

It took quite a bit of formatting, but we got there in the end.

Automatically highlighting a line

If we include Slopegraphs on regularly updated reports we do not want to manually highlight the lines each time. By using IF statements it is possible for the chart to highlight the significant items automatically.