Slopegraph in Excel

Sometimes the primary analytical task at hand is to understand what happened between two points in time. For example, did the government receipts as % of GDP—by country—increase, decrease or stay unchanged between this year and one decade ago?

There are different ways to present change between two points in time. Slopegraphs, Range Bar Graphs and Deviation Graphs (both in magnitude and percentage) are three different approaches that may be used to show change in time. To each its strengths and weaknesses.

Slopegraphs such as the beauty attached—produced by Edward Tufte in his The Visual Display of Quantitative Information (page 158)—organize complex information for viewing patterns and trends. The graph shows—for selected countries—government receipts as a percentage of GDP for two years. It plots 1970 composition down the left and 1979 composition down the right. The position of the labels on the left or right, makes it easy to get the ranking for either 1970 or 1979. The slopes of the lines make it easy to compare countries across years, that is, which is up or down and by how much.

Unfortunately, Excel charting library doesn't include native capabilities to produce Slopegraphs. Users are either obliged to resort to VBA or to take the time consuming task of following a long list of steps to create one.

So here I'm offering you an Excel template that takes a data set as an input and creates a Slopegraph— including a Range Bar Graphs and Deviation Graphs (both in magnitude and percentage). You will also get a PowerPoint template for the same graph. The Excel model has a summary worksheet (named PowerPoint) that organizes the data in a way compatible with the data input required by the PowerPoint template.

The Excel model has the following capabilities:

- Sort categories by alphabetical order, variable 1 (i.e. 1970), variable 2 (1979), the change between the two variables (either in absolute or in %) or as per the original data source.

- Sort categories by descending or ascending order.

- Adjust the gap the category name and corresponding data value.

- Adjust the gap between consecutive value.

- Define the number formatting of the labels.

- Define the gap between the two columns of data.

- Define the safe distance between labels in order to avoid overlap.

- Stacks labels horizontally while maintaining a minimum distance between the labels. The template can handle up to 9 overlapping labels. Anything above 10 will be lumped together and needs to be adjusted manually.

- Show % change in labels (including small arrows for direction of change).

- User-friendly interface to adjust the above settings.

- Data entered into Excel Table—hence the chart and formulas update automatically when the datasource expands or shrinks.

- The size of the data matrix is only limited by Excel's charting and calculating capabilities.

- Fully customizable chart using standard Excel functionality enabling you to design a large variety of charts.

- Unlocked workbook/worksheets with ability to expand template to suit your needs.

System requirements

The template relies heavily on Excel's Dynamic Arrays capabilities, only available to Microsoft 365 subscribers.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.