How to Use the SLOPE Function in Excel (Examples)

The SLOPE function in Excel is a powerful statistical function that lets you calculate the linear regressing through a set of data points 🧾

It essentially helps you understand how the changes in one variable are related to the changes in another variable. You can make predictions and get insights into data and trends by finding the slope.

Whether you’re handling basic data sets or working with hundreds of rows of data, the Excel SLOPE function can help you get your desired result ➗

In this guide, we will see in detail what a SLOPE function is, how it is used and some common but practical examples of it. Download our sample workbook here to practice along.

Table of Contents

What is the SLOPE function?

The SLOPE function returns the slope of the linear regression in the x and y-axis. It is the division result of vertical and horizontal distance between two data points on a line, i.e., the rate of change between x and y values 🧐

It shows how much the y-value changes for a single unit increase in the x-value. This function is critical for trends and regression analysis as it tells how a certain variable changes over time.

Its syntax is given as follows:

Click to copy

where,

known_y’s refers to the array of y values or dependent variable

known_x’s refers to the array of x values or independent variable

It uses known x-values and known y-values to find the slope of the straight line 📈

How to use the SLOPE function

Now that we know what the slope function is, let’s see how to use it. There are three different examples of its usage below that will help you understand how a SLOPE function works.

Basic Usage

We have the following sample dataset. We will use the known x and y values to find the slope of our data set.

Sample data set

To do that,

Step 1) Select cell C2.

Step 2) Type in the following slope formula: ⌨

Click to copy

Step 3) Press Enter.

Value returned

The formula will return the slope of the x and y-intercept as seen above 🔼

Make sure you first input the y values and then the x values, otherwise the slope value returned will be different.

Kasper Langmann, co-founder of Spreadsheeto

Using Named Ranges

In named ranges, the range of cells is given a certain name. The column containing y values could be labelled as Y-Axis and x values could be labelled as X-Axis.

In the following data set, both the columns are named ranges. We will use these in the formula.

Sample dataset

To do that,

Step 1) Select cell C2.

Step 2) Type in the following slope formula:

Click to copy

Step 3) Press Enter.

Value returned

The formula will return the slope of the X and Y named ranges as seen above.

How easy was that? 😃

Note that the SLOPE function only works on two-dimensional data. If the data is one-dimensional or three-dimensional, you will need to use a different formula.

Kasper Langmann, co-founder of Spreadsheeto

Handling Non-Adjacent Ranges

If your data set has a non-adjacent range, you can still use the SLOPE function to find its linear regression. This might happen when the data is unorganized and you need to find the slope quickly 😀

We have the following sample data set and we want to find the slope of the non-adjacent ranges.

Sample dataset

To do that,

Step 1) Select cell D2.

Step 2) Type in the following formula:

Click to copy

Step 3) Press Enter.

Value returned

The function returns the slope of the non-adjacent ranges in no time. Cool no? 🤓

Visualizing with a Scatter Plot and Trendline

When you use a SLOPE function for linear regression analysis, visualizing it with a scatter plot or trendline is highly recommended. Here’s why: 🤔

Clarity: Visual representation of a data set makes it easier to under the relationships of the variables in it. Seeing the regression line on the scatter plot makes it easier to grasp how the line fits the data.

Trends & Patterns: By graphing, you can identify the trends and patterns you might not have been able to spot earlier. It lets you see the strength and direction of your dataset very clearly.

Anomalies: Scatter plots can help you spot anomalies or outliers in your data. They could be affecting the overall calculation and analysis of the slope.

Model fit: By seeing the linear regression data line, you can determine how well it fits the data. If it sits close to most of the data points, this indicates it is a good data fit while meeting fewer data points indicated otherwise.

Comparison: If you have different data models, you can compare them on the scatter plot to find their differences easily 😉

Let’s now see how to create a scatter plot. We will use the same dataset as earlier.

To do that,

Step 1) Select your entire dataset – both the X and Y values.

Step 2) Go to the Insert tab and Select the Scatter charts from the Charts section 📊

Step 3) Select Scatter with straight Lines from the dropdown.

Chart type

Step 4) The Scatter chart will appear on the Excel sheet.

Chart appears

Step 5) Right-click on any of the series to open a dropdown.

Step 6) Select Add a Trendline from the menu.

Add trendline

Step 7) On the right side of the chart, the Format Trendline pane will appear.

Step 8) Under the Trendline Options select Linear 📈

Linear

Step 9) Check the Display Equation on the chart option.

Display Equation

Step 10) Close the side pane.

Step 11) The equation will be displayed on the chart which includes the slope too.

And it’s done! The Scatter plot with a trendline will display the slope of the graph as follows:

Scatter chart appears

A Positive slope value tells that the slope is inclining upwards whereas a negative slope value indicates that it is moving downwards.

Kasper Langmann, co-founder of Spreadsheeto

How cool is that? 😁

Points to remember

The SLOPE function is slightly tricky to use. It has quite some requirements that need to be fulfilled before you can use it. Let’s look at these below.

  1. The x and y values must either be numbers, arrays, names or references with numbers.
  2. The known_x and known_y values must be equal in length and the variance of the known_y’s should not be zero.
  3. If any value of the x or y argument is empty, textual or logical, the SLOPE function ignores it. However, any argument with zero value is included.

Troubleshoot a SLOPE error

The SLOPE function throws some errors when the user makes a mistake or does not provide the correct data set. Let’s see what these errors are below.

  1. SLOPE function returns a #DIV/0! error when you try to divide by empty or zero-value cells.
  2. SLOPE returns #N/A error when the count of y values is not equal to x values or vice versa ❌

Conclusion

In this guide, we saw how to use the SLOPE function. It is an invaluable tool for you if you want to analyze the relationship between two variables through linear regression 😵

With the help of the example above, you can easily find the slope of your data set. To better understand the findings or slope of the linear regression line, you can visualize your dataset with scatter plots or trendlines 📈

To learn more about functions and linear regression in Excel, read the following articles.

How to do Linear Regression in Excel: Full Guide (2024)

How to Add a Trendline in Excel – Full Tutorial (2024)

How to Calculate Present Value (Excel Function PV)

We hope this article helped answer all your questions regarding the SLOPE function 🤗