How to Create a Slope Chart in Excel?

Our brains can easily interpret connections and patterns. Besides, we can easily distinguish between slopes of straight lines.

And it’s for this reason that seasoned data visualization experts use Slope Charts in their data stories. These charts are easy to read and understand. Besides, this makes them ideal if you want to communicate high-level insights quickly to a non-technical audience.

Creating a Slope Chart does not have to be a complex and time-consuming undertaking. In this blog, you’ll learn how to create a Slope Chart in Excel. You’ll also learn how you can use these charts to create compelling data stories.

The sensational strategies and tips we’ve rounded up for you are tested and proven. We use these tips to create data stories for our clients, meetings, and presentations.

We’re confident you’ll gain immense value from the blog.

Table of Contents:

1. What is a Slope Chart?
2. Components of a Slope Chart in Excel
3. Why Does a Slope Graph Matter to You?
4. What’s The Best Visualization Tool to Use to Create a Slope Chart?
5. How to Create a Slope Chart in Excel: Quick and Easy Steps
6. How to Install ChartExpo In Excel To Access Slope Chart Template For Free
7. Converting Data into Slope Graph in Excel
8. How to read a Slope Chart in Excel
9. When to Use a Slope Chart
10. Slope Graph Best Practices
11. FAQs
12. Wrap Up

Video Tutorial:

What is a Slope Chart?

Definition: Slope Charts are simple graphs that quickly and directly show transitions, changes over time, absolute values, and even rankings. Besides, they’re also called Slope Graphs.

You can use this chart to show the before and after story of variables in your data.

Slope Graphs can be useful when you have two time periods or points of comparison and want to show relative increases and decreases quickly across various categories between two data points.

The best way to explain the value of and use case for slope graphs is through a specific example.

Imagine you intend to communicate data from a 2019 road accident report. To show the relative change in road accidents from 2000 to 2019, the slope might look something like the image shown above.

This chart packs in a lot of information. In addition to the absolute values (the points), the lines that connect them give you the visual increase or decrease in the rate of change (via the slope or direction).

Note: Slope Charts require immense patience to create because you can’t find ready-made templates in most data visualization tools.

Whether a slope chart will work in your data storytelling or not depends on the nature of your data. For instance, if many lines are overlapping, your chart will lose effectiveness. And this is due to clutter.

However, you can overcome the above challenge to some degree by highlighting a single line category that has either increased or decreased over time.

Edward Tufte first used the Slope Charts in his The book Visual Display of Quantitative Information– to describe the hierarchy of countries between 1970 and 1979.

Components of a Slope Chart in Excel

A slope chart has features identical to those of other graphs, such as a line chart. Let’s take a quick look at these features.

• Data

Data is the most significant part of making this chart for your story. Essentially, you need a substantial amount of data before you decide to use this chart for your visualization.

• Plot

You need a tool that’s easy to use to plot this chart for your data story. There are a plethora of tools for data visualization, such as Excel.

• Labeling

Remember, we naturally read starting from the left. Besides, it makes sense to label the axes (on the left side) to guide your audience towards the meaning and context of your insights.

• Legend

The legend may be the colors used or is a side section of the chart that gives a short text description of each series. Use a legend to help your audience understand the charted data.

• Title

A title is the big idea you want to communicate to your audience. A title that compels the audience to pay attention and take the recommended action should convey what’s at stake.

For instance, ‘Our Sales have declined by 65% in the last 7 years. And at this rate, we won’t be in business in the next 2 financial years.

And this brings us to the benefits of these charts.

Why Does a Slope Graph Matter to You?

This is one of the easiest charts to read and interpret. Essentially, your audience will not struggle to understand the key insights. Besides, it’s incredibly easy to draw slope Graphs if you use the right visualization tool.

With Slope Graphs, you don’t need much customization to highlight key insights. Again, it depends on the tool you’re using for the exercise.

Change is around us. Business change is critical because it can drive profits or result in monetary loss. So you need the best chart in your data story to help persuade the management. The best chart to visualize change over time in your data is the Slope Graphs.

Using this chart can help you easily forecast the results of data that are yet to be collected.

So how can you tell a story using this visualization?

What’s The Best Visualization Tool to Use to Create a Slope Chart?

Let’s talk about Excel because it’s the tool most of us have known since childhood. This tool has been there for decades. Besides, it has an incredibly friendly user interface even for non-technical users.

It’s safe to say that Excel is a friend to a massive chunk of people who work with data. We mean, it’s the go-to tool if you want to visualize data quickly and affordably.

So how can you create a Slope Chart in Excel?

How to Create a Slope Chart in Excel: Quick and Easy Steps

Let’s visualize the data below using the Slope Chart in an Excel spreadsheet.

Let’s delve into our Excel without wasting time.

• • Create the chart by highlighting the data in Cells A1-C8.  From the menu click Insert -> Charts -> Line Chart(or Line Chart with Markers, if you want markers)

• • Use markers as they indicate where the end of the line is, but either option is acceptable. See the chart below.

• • Next, right-click on the chart area and click Select Data, as shown below.

• Within the Select Data Source window, there are two main boxes on the left and right sides.
• Switch these boxes over, as shown below.
• Click the Switch Row/Column button, and then click OK.

Now we’re starting to get somewhere. 

Note: 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).

Creating a Slope Chart in Excel is incredibly time-consuming if you give it proper formatting.

So what’s the best tool that comes loaded with pre-existing templates for a slope chart?

Keep reading because you don’t want to miss the juicy part that’s coming next.

So what’s the Solution?

The solution is not to ditch your beloved Excel. No, we’re not advocating you to do away with the spreadsheet tool you’ve probably known since childhood.

You just need to supercharge it with a third-party add-in to make it a reliable partner for data visualization.

Microsoft knew very well it was impossible to cater to all the data visualization needs you may have. That’s why they came up with an app store where you can access third-party add-ins to get various specialized visualization tasks done with ease.

Well, there’s a reliable and incredibly easy-to-use add-in called ChartExpo. It comes jam-packed with Slope Chart templates and other 80-plus charts.

What is ChartExpo?

ChartExpo is a cloud-hosted add-in that transforms your Excel spreadsheet app into a highly responsive data visualization tool.

Wait! That’s not all. 

This highly affordable data visualization tool comes with over 50 chart templates to grant you a broader choice of visuals to select. With ChartExpo, you don’t need to know programming or coding. Yes, it’s that easy peasy to use.

When you’re curating a data story, feel confident you have a reliable data visualization buddy on your side. ChartExpo provides you unlimited freedom to customize your Slope Graph to align with your data story.

Remember, you can highlight the key insights you want your audience to take in with ease. You just need a few mouse clicks to access a Slope Chart for Excel that fits seamlessly within your data narrative.

More Money-saving Benefits For You!

• ChartExpo has an in-built library with 50-plus charts to ensure you’ve got enough stock to test for the best.
• ChartExpo add-in for Excel comes with a free 7-day trial.
• Essentially, if you’re not satisfied with the tool within a week, you can opt out as quickly as signing up for a trial.
• The number of paying users after the trial period is over a staggering 80%. And this means that ChartExpo does not disappoint.
• ChartExpo add-in is ONLY $10 a month after the end of the trial period.
• You have a 100% guarantee that your computer or Excel application won’t be slowed down. If it does, just opt out of the risk-free 7-day trial.
• You can export your insightful, easy-to-read, and intuitive charts in JPEG and PNG: the world’s most-used formats for sharing images.

Let’s head to the meaty part of the blog: the section where you get to practice what you’ve learned.

Follow the simple steps below in preparation for the next section.

How to Install ChartExpo In Excel To Access Slope Chart Template For Free

ChartExpo offers immersive charts, which can make a massive difference in the way data visualizations impact your decision-making.

To Get Started with ChartExpo for Excel add-in, follow the Simple and Easy Steps Below.

• Open your Excel application (Excel 2013 with sp1) or later).
• Open the worksheet and click on the Insert menu.
• Click My Apps and then click on See All, as shown.

 

• Search for a ChartExpo add-in in my Apps Store.
• Click on the Insert.

 

• The add-in will be added to the Excel application, as shown.

 

• Log in with your Microsoft account or create a new account.
• Enter your newly created account and start using the ChartExpo add-in. You’re only required to log in for the first time. Next time, ChartExpo won’t ask for your details.

• Open your ChartExpo. By default, you will see a list view of charts.

• You can access 6 different categories of charts by clicking on Category.

Converting Data into Slope Graph in Excel

Imagine you run a Google Ad campaign, and you want to compare traffic interaction (clicks) by users from mobile for various geographical locations.

Essentially, you’ll be comparing performance between the previous versus current period.

Let’s use the tabular data below for our scenario

Period	State	Clicks
Previous	Chicago, Illinois, US	14
Previous	Dallas, Texas, US	7
Previous	Houston, Texas, US	12
Previous	Austin, Texas, US	14
Previous	Albany, New York, US	31
Previous	Trenton, New Jersey, US	24
Previous	Newark, New Jersey, US	22
Previous	Helena, Montana, US	8
Previous	San Antonio, Texas, US	7
Current	Chicago, Illinois, US	21
Current	Dallas, Texas, US	27
Current	Houston, Texas, US	30
Current	Austin, Texas, US	69
Current	Albany, New York, US	21
Current	Trenton, New Jersey, US	34
Current	Newark, New Jersey, US	21
Current	Helena, Montana, US	21
Current	San Antonio, Texas, US	28

Now put the data in Excel and select your relevant chart which is the slope Graph from the list.

Make sure you select all the columns on which data needs to be drawn and click on the “Create Chart From Selection” Button as follows.

Your slop chart is ready to present your click data.

Insights

• • Clicks from mobile users based in Albany are currently low compared to the previous period.

• • The best-performing location for clicks currently is Austin, Texas. Even San Antonio also improved from its previous performance to the current.
  • The profitable action to take is to increase bidding for the above-mentioned location to drive more clicks.

• The worst-performing location for users’ clicks is Newark, New Jersey. The probable cause for this is the lack of optimizing the ads for mobile.

And this brings us to the next section, where you’ll learn how to interpret the chart.

How to read a Slope Chart in Excel

Let’s take a basic math detour.

Generally, a slope is a measure of the steepness of a straight line when plotted on a pair of coordinate axes [x, y].

Letter m will indicate the amount that increases or decreases the dependent variable y when the independent variable x increases by one unit. And is represented by the equation y = mx + b,

• A positive value of the slope implies that the dependent variable increases.
• A negative value implies that the variable y
• The zero value of the slope implies a horizontal line.

There is a wide range of real-world processes, phenomena, scenarios, and systems modeled using straight lines. And naturally, slope charts are strategically positioned to tell the associated story.

A slope chart comprises two parallel vertical axes that indicate the same type of categorical variable with the same units of measurement.

While there is no horizontal axis, the distance between the vertical axes indicates an ordinal scale or a time interval. The numerical values of each category are indicated with points on each vertical axis and linked by a straight line or a curve.

The slopes of these lines or curves reveal the changes in absolute or relative values over time for each category. The information can also be coded using lines of different colors or different thicknesses.

So when should you deploy these charts?

When to Use a Slope Chart

• Use this chart in your data story because it shows high-level insights, such as rankings, and changes over time easily. Your audience won’t struggle to decode the key insights at the center of your data story.
• Use Slope Graphs to show the differences between two related points, changes over time, or changes in the formation of a group (e.g., part vs. whole).
• You can also use a Slope Chart to visualize ranking data.

Let’s head to the best practices section recommended by the pioneer of Slope Graphs: Prof. Edward Tufte.

Slope Graph Best Practices

• Be clear — first to yourself, then to your audience — whether your numbers are displaying the items in order or whether they’re on an actual scale.
• An important consideration: is the primary purpose of the chart to show the relative rate of change for each item over time? The absolute values for each item? Or both pieces of information?

Remember, knowing this will help you make decisions about scaling and labeling.

• If the data points or labels are bunching up, expand the vertical scale as necessary to prevent clutter that may obscure key insights.
• Left-align the names of the items on both the left-hand and right-hand axes to make vertical scanning of the items’ names easier.
• Include both the names of the items and their values on both the left-hand and right-hand axes.
• Use a thin, light gray line to connect the data. Note a too-heavy line is unnecessary and will make the chart harder to read.

FAQs

What is a Slope Chart?

Slope charts are simple graphs that quickly and directly show transitions, changes over time, absolute values, and rankings.

This chart can be useful when you have two time periods or points of comparison and want to quickly show relative increases or decreases across various categories between the two data points.

What is a Slope Chart used for?

A slope chart is useful when you have two time periods or points of comparison and want to quickly show relative increases or decreases across various categories between the two data points. You can use it to visualize a wider range of data in business and other areas.

What are the best practices to follow when creating a Slope Chart in Excel?

According to Prof. Edward Tufte, the pioneer of these charts:

• Be clear and concise
• If the data points or labels are bunching up, expand the vertical scale as necessary.
• Left-align the names of the items on both the left-hand and right-hand axes
• Use a thin line to connect the data points

Wrap up

A Slope Chart is one of the graphs that are easy to read and interpret, especially among non-technical audiences.

So you can use this visualization to create data stories that are irresistible for your audience irrespective of their position in the corporate power hierarchy.

Creating a slope chart in Excel does not have to be overwhelming or time-consuming. Yes, if you’ve just landed here, please read the rest of the blog to gain invaluable and thought-provoking tips regarding data storytelling using Slope Graphs.

We’ve compiled complex and time-consuming steps of creating a Slope Chart in Excel just to prove there’s a better alternative. An alternative that can save an immense amount of time and still create visualizations that make your data story compelling.

What’s the time-saving alternative? Use the ChartExpo library.