Make a Slopegraph in Excel

Slopegraphs are a newer graph type with powerhouse capabilities. They rely on Excel’s line graphing feature but they don’t necessarily have to show change over time. Slopegraphs play into our ability to judge slope fairly well. For this reason, they are perfect for highlighting the story of how just one category decreased when others increased, or to show that one increased at a rate much faster than the others.

Let’s say we are comparing sales in each department of a grocery store, before and after they moved locations. As a side by side bar graph, it would look like this:

Figure3.3_GrocerySidebySide

Even though humans are good at detecting length, this graph is somewhat difficult to digest. It’s a bit hard to see that sales of cheese are actually down at the new site, for example. It’s easy to miss that just one New Site bar is shorter than its corresponding Old Site bar. A slopegraph will make the story clear.

The table for a slopegraph is probably quite familiar: just two columns of numbers.

Figure3.4_GroceryTable

You will highlight the rows and columns and insert a line graph. I prefer a line graph with markers, but maybe I’m just obsessed with dots.

Figure3.5_GrocerySetUp

It doesn’t quite look right – the departments are on the x-axis and they shouldn’t be. Click your old friend Switch Row/Column.

Figure3.6_GroceryGraph1

You can see the beginnings of the slopegraph in this modified line graph already. The differences are minor but interpretively important. Slopegraphs usually have no space between the end of the line and the end of the plot area. The lines are pushed to either side of the graph. To delete that gap, we are going to click a single magic button inside Excel. Really, this is pretty much where it all happens.

Right-click on the x-axis and select Format Axis from that menu. In the Format Axis box that opens, look for the area with the heading Axis position. Under that, pick the radio button next to On tick marks.

Figure3.7_GroceryAxisFormat

By default, Between tick marks is selected but On tick marks will push the lines to the edges of the graph.

Figure3.8_GroceryGraph2

From here on out, it’s all formatting baby!

Slopegraphs tend to be long and narrow, unlike line graphs, which are usually wide and short. So resize the graph by stretching the corners. This also puts some distance between points that are nearly on top of one another.

We also need some space on either side of the line to add labels. Currently, there’s a y-axis there. Delete the y-axis (just click on the numbers and hit the Delete key). Do the same for the y-axis gridlines. That buys a little bit of room on the left, but not enough. So click inside the plot area – the white space in the middle of the graph – and drag it’s side handles in on both sides. The graph space will narrow but the overall chart area will stay the same.

Figure3.9_GroceryPlotShrink

At this point, it should already be really obvious that one line is going down. We can also see that another line is increasing moreso than the others. Slopegraphs highlight this story better than any other chart type. As we continue to format the chart, we will use action colors on those two lines to bring attention to them and the other lines will go gray.

Now it’s time to delete the legend and label each point of the line. With the slopegraph, line labelling is a little tedious. You can only add labels to one line at a time. Also, I like to add the label to one side and just the number to the other, so there are a lot of clicks involved. Here’s the most streamlined way to do it and we will alter the colors in the process.

Right click on the top line and select Format Data Series. In the box that opens up, change the line color, marker fill color, and marker border color all to a medium-light shade of gray.

Figure3.10_GroceryLineChange

The line should still be highlighted inside your graph. Right-click on it and select Add Data Labels. This will add the dollar values to both markers. Click on the labels and change their color to match the lines. With those labels highlighted, carefully click on just the left label so that it is the only one highlighted. Then right-click on that label and select Format Data Label (it should be singular). New box opens! Check Series Name so that the right grocery department label will be added, but only on the left. Uncheck Show Leader Lines (this will matter more later). Personally, the comma that separates the label from the value offends me. In the Separator drop down menu, pick the option that says (space). Then look for the Label Position choices and pick Left.

Figure3.11_GroceryLabels

Yes, lots of steps there but the payoff is clear labeling and smart color coding that support the story inside this data. Repeat this process for the rest of the lines, choosing a medium dark color for the rapidly increasing line and a dark color for the decreasing line.

Figure3.12_GroceryAlmostDone

As you labeled these lines you probably noticed that the labels for Packaged and Frozen were overlapping each other. To fix that, just click on each label and drag it up or down a little to space them apart. This is why we unchecked Show Leader Lines earlier. With that box checked, each label would have an ugly, cluttering little line tying it to its part of the slopegraph.

Its ok add a great title and stop here. I like to add a final touch by inserting vertical lines that shoot up from Old Site and New Site. Right-click on the x-axis and in that menu, select Add Major Gridlines. BOOM! I also delete the x-axis line. To do so, right click on the x-axis again and select Format Axis. In the new box, look for the line color area and select no line.

Figure3.13_GrocerySlopegraph

The slope of the slopegraph markedly accentuates increases and decreases when comparing two sets of numbers.

This post is an excerpt from my latest book, Effective Data Visualization. It has loads of advice on the best chart type to use and how to make it in Excel.


Learn in the Academy!

You can find step-by-step instructions on how to make 60+ awesome visuals in my Evergreen Data Visualization Academy.

Video tutorials, worksheets, templates, fun, and a big-hearted super-supportive community. Learn Excel, Tableau, R or all three. Come join us.

Enrollment opens to a limited number of students only twice a year. Our next enrollment window opens April 1. Get on the wait list for access a week earlier than everyone else!

Master Dataviz with Graph Guides!

Our newest program, Graph Guides, is a custom-built, year-long sprint through 50 Academy tutorials.

When you enroll, we’ll assess your current data viz skill set, build you a customized learning path, and hold your hand as you blaze your way to new talents.

We open enrollment to 12 students at a time and only twice a year. Get on the waitlist for early access to our next enrollment window.

17 Comments

  1. I love slope graphs! Normally I hate any graph that’s truncated (unless it’s a “call out” of a graph with the full axis) but in this case, could you truncate the axis down so there’s not so much white space underneath the lowest line?

    1. Stephanie Evergreen

      You could, Sara. It’s always a tricky situation, with strong supporters for and against. But my rationale is that if 0% isn’t a natural part of your data set, you are ok to truncate line and slopegraphs.

  2. I’m so glad I read this blog carefully because I picked up a new detail! I’ve always hated the comma separator too, when you want the data label to indicate both category and value, but I never noticed that separator option drop down menu! It really pays to be a careful reader of all pop-up and drop down menus. Excel can do a lot more than I’ve given it credit for. Thank you!!!

  3. Thanks for the step by step instructions. It looks like you are using a new version of Excel than me. I don’t have a left label position choice, but Excel will let me drag the box over. For longer labels, Excel 2010 automatically wraps the text. Do you know of a way to turn off the text wrap on the data label?

  4. I agree slopegraphs can be great and effective visualization tools. Unfortunately it’s a pain to build up a dynamic version (eg. add new data) in Excel.
    I think it is important to have data labels on both sides – as we can see in Edward Tufte’s version – to have clear picture in case of more data. Some time ago we played a bit with this chart type in Excel, on top of solving the dynamism and labels problem, we created a version with a special highlight effect based on mouse rollover technique. if you are interested, you can see our solution here:
    https://sites.google.com/site/e90e50fx/home/Edward-Tufte-slopegraphs-in-excel
    Cheers,
    Kris

  5. I was struggling to show percentage change over two time points, and I ended up using a slopegraph. Even though all the variables started at 0, it was a really useful way to visualise some really tricky data. Thanks for the tutorial.

  6. Great tutorial, Stephanie! Have you figured out any way to change which lines come to the front? For instance, your model the black cheese line goes underneath/behind one of the gray lines. But what if I don’t want it to be obscured by that gray line? I’m having this problem in my slope charts. My darkest line is distinct only because it is the darkest color (which helps, but it would be even better if it were on top instead of trapped behind some of the lighter lines). Thanks!

  7. Hi Stephanie! I love using your slopegraph template as an alternative to horizontal bar charts and dot plots (especially for pre-test/post-test data). I was asked if we could plot a minimum and maximum number on each of the y-axes in addition to the data labels. Is this possible to do?

    1. Stephanie Evergreen

      Sure – just leave in your y-axis scale instead of deleting it. You can also change the intervals of the y-axis so you only label the top and the bottom, or something less obnoxious than every 10%.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.