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

The data

We will use the same data as before, but now we have duplicated the columns twice (don’t worry all will become clear).  The formulas in Columns D to G are linked to the values in Columns B and C.

Create the chart

Using the values in Cells A2-A9 and D2-E9 create a chart using exactly the same method as before, but keep all the lines mid-grey.

Next, select the cells in A2-A9 and F2-G9.  Copy the cells (Ctrl + C, or Home -> Copy).  Click on the body of the chart, then from the Home menu select Paste -> Paste Special.

From the Paste Special window select New series, then click OK.

The Slopegraph will now have the original data from Columns D and E, plus the values in Columns F and G directly over the top.  Format the new lines which have been pasted on top in the color you wish.

Adjust the data

We now need to go back and correct the data, so that it creates the automatic highlighting.

Charts in Excel have a useful feature whereby the #N/A value is not displayed on the chart.  So, we just need to combine an IF function with the NA() function to create this (see the table below).

The adaptability of this is partly down to your ability to create IF statements which meet your requirements.  You can use additional helper columns, or other formula tricks.  The only important thing is that each pair of numbers can exist in Columns D and E or Columns F and G, but not in both.  Equally the #N/A value can only exist in one pair of columns.

In my example I am using an Array formula to find the Maximum movement.

The data above creates the chart below.

If the values in B and C change the chart will update automatically, to highlight just the items you wish.

Dual measure Slopegraphs

Slopegraphs are also great choices for comparing data across two related measures, for example, Return on Investment or Gross Profit Margin.  The challenge is how to create the dual measures.

The axis on this chart is a trick.  It is created through the same method as a single measure chart, but we will use a data label to display the second measure.  Stick with me, you’ll see what I mean.

The data

For this next example we will consider projects, specifically the cost of investment and return provided.

Columns B and C provide the basic data.  In cells B11 and C11 the MAX function is used to obtain the maximum value in each column.  In Cells B12 to C12 I have used the MIN function to obtain the lower of the lowest value in the column or zero.

The values in Column D are set to equal Column B.

The key to success with this chart is in Column E.  Cell E3 contains the following formula:

=C3/($C$11-$C$12)*($B$11-$B$12)

The only purpose of this formula is to recalculate the Return on a proportionate basis to the Investment.  This formula has been copied down into Cells E4-E9.

Cells F3 – F9 are all set to #N/A using the NA() function.  The values in Cells G3-G9 are set to equal the value in Cells E2-E9.

Creating the chart

Select Cells A2-A9 and D2-E9.  Create the chart following the same method as we used single measure chart, but this time, do not label the points on the right.

Select Cells C2-C9 and F2-G9.  Copy the data and Paste Special a New Series, just like the instructions for automatic highlighting.

Column F is all #N/A, so those points are ignored.  Column G contains the values from Column E.  These markers sit directly be directly on top of the existing markers from Column E.  Add Data Labels to the new markers on the right (i.e. the markers based on Column G).  These data labels need to be set to display the Series Name (which is from Column C) and not the value.

Finally, manually set the highlighted lines and markers as before.  Now your chart should look something like this.  That’s a pretty clever way to display Return on Investment, right?

Another enhancement could be to set the Minimum and Maximum values so that a horizontal line represents something.  For example, if the minimum acceptable return on an investment is 20% make the horizontal line equal the a 20% return. Then, every upward slope is a good investment and every downward slope is a bad investment.

Dual measure automatic highlighting Slopegraphs

It is possible to create a dual measure automatic highlighting Slopegraph.  We have covered all the necessary techniques, so I am not going to cover it in detail.  But have a go yourself.

Save

Save

Save

Save

Save