How to Use Excel SLOPE Function (5 Quick Examples)

Microsoft Excel provides various types of functions to make the work more convenient. Excel SLOPE function is one of them. In this article, we will learn about five quick examples of the Excel SLOPE function. To clarify, we’ll go over several examples to show you how to use this function appropriately.

Overview of Excel SLOPE Function

• Description

The SLOPE function returns the slope of the linear regression line through known y’s and known x’s data points. The rate of change along the regression line is calculated by dividing the vertical distance by the horizontal distance between any two locations on the line.

• Generic Syntax

SLOPE(known_y’s, known_x’s)

• Argument Description

• Returns

The SLOPE function returns a numeric value.

• Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003.

What is SLOPE?

The SLOPE function in Excel is classified as a statistical function. Calculating the slope of a line using values provided by two coordinates is a common task in statistics. The SLOPE function can be used for not only statistics but also in financial analysis.

Remarks:

The SLOPE equation determines the slope of a linear regression line is given below:

Here, where x and y are the samples means AVERAGE(known_x’s) and AVERAGE(known_y’s).

SLOPE Function in Excel: 5 Uses

1. Calculate Slope of a Regression Line Using Excel SLOPE Function

In this section, we will use the SLOPE function to determine the slope of a regression line. We have a dataset of two columns describing sales value as well as corresponding profit.

We will calculate the slope in cell C11. Let’s go through the steps regarding this solution.

• First, select cell C11. Insert the following formula and press Enter.

• Here, we can see the value of the slope in cell C11.

• Now, we will create a 2D line chart to visualize the functionality of the SLOPE function.
• Select cell (C5:C9). Go to the Insert tab.
• Then, from the dropdown of the line graph, select the first option of the 2-D line section.

• Here, we will get a graph like this.

Here, we can see the profit values in the Y-axis. Besides, we want to plot the sales values on the X-axis. So, to do this, we will take some steps.

• Right-click on the graph and select the option “Select Data”.

• We will see a window like this. Select the Edit option under Horizontal Axis Labels.

• Select Axis label range column (B5:B9) and click on OK.

• So, we can see the sales value along the X-axis.

• Also, we will input the Axis Titles and Legend names from the Chart Elements.

We can also insert a trendline to understand the steps of the slope. To this-

• Do a right-click on the line of the graph. Select Add Trendline option.

• After this step, we can see a trendline like the below image.

• Finally, we will get a figure like the one given below after completing all the steps.

2. Use of Excel SLOPE Function to Calculate Negative Slope

The slope value of our previous example was positive. It can also have a negative value. In this example, we will see an example of a negative slope. In this case, we have a dataset of Budget and Expense. Let’s determine the slope of this dataset.

• Firstly, select cell C11 and insert the following formula:

• Then press Enter. We will see a negative value of slope in cell C11.

• Finally, follow the steps of the previous example to insert a graph. We will get a graph of a line having a negative slope.

Read More: How to Find the Slope of a Line in Excel

3. Pairing SLOPE and INTERCEPT Functions Together

We are discussing the Excel SLOPE function. We can write an equation with the pairing of SLOPE and INTERCEPT functions. To pair these two functions, we have to know not only the value of the slope of a dataset but also the value of the intercept part.

• Firstly, select cell C12. Insert the following formula:

• Next, press Enter. We will get the value of the intercept part in cell C12.

• Finally, following the equation of a straight line, we can write a new equation with the value of slope and intercept part. The equation of the line is-

Y = mX + C

Here,

Slope = m

Intercept = C

Here, the INTERCEPT function returns the y-axis intersection point of the line using x-axis values as well as y-axis values.

Read More: How to Calculate Slope and Intercept in Excel

4. Use of Excel SLOPE Function as VBA Function

We can apply the SLOPE function in VBA (Visual Basic for Application) to calculate the slope of a dataset. In this case, we will demonstrate this example using the sales data that we used in previous examples. So, we have given the dataset again in the image below.

It should be noted that if we do not have the Developer tab in our Excel, we have to display the Developer tab in the ribbon to create macro-enabled content.

First, we will see how to activate the Developer tab. Just follow the instructions given below.

• Go to the File option in the top-left corner of our Excel.

• Next, select the Options.

• Then, a new window will come. Select the option Customize Ribbon from the available options.

• After that, select the Developer option and click OK.

• Finally, we can see the Developer tab in our Excel.

Now, we will use the Developer tab to create macro-enabled content. Let’s see how we can do this in the following steps.

• Go to the Developer tab. Select the Visual Basic option.

• Here, a new window will open. From the window select the Insert tab. From the drop-down, select the Module option. We will get a new module named Module-1.

• Select the option Module-1. A blank window will open. Insert the following code in the blank window.

Sub SLOPE_Example()
Dim Sales As Range
Dim Profit As Range
Set Sales = Range("B5:B9")
Set Profit = Range("C5:C9")
MsgBox Application.WorksheetFunction.SLOPE(Profit, Sales)
End Sub

• We will Click on the run option that we can see in the below image. We can press F5 as well to run the code.

• Finally, we can see a message box with the value of the slope.

Read More: How to Find Instantaneous Slope on Excel

5. Excel Slope Function Errors

5.1 #N/A! Error While Using Excel SLOPE Function

When the specified array of known x’s and array of known y’s are of different lengths, the SLOPE function throws a #N/A! Error.

We will see this error with the following dataset that we used for previous examples.

• Firstly, we will select cell C10 and insert the following formula:

• Finally, press Enter. We can see that cell C10 is showing #N/A! error. At this time it happens because the value of (C5:C9) is not equal to (B5:B10).

5.2 Excel SLOPE Function giving  #DIV/0! Error

The SLOPE function gives #DIV/0! Error in two cases:

1. If the variance of the specified known x’s evaluates to zero, the SLOPE function throws the #DIV/0! error.
2. Any of the known x’s or known y’s arrays are empty.

Hence, we will calculate the slope of the following dataset in cell C10.

• Select cell C10 and insert the following formula:

• Then, press Enter.
• Finally, we will see an error named  #DIV/0!. The reason behind this error is the empty value of cell (B5:B9). 

Things to Remember

1. The parameters of the SLOPE function must be numbers or names, arrays, or references to numbers.

2. On the other hand, the Error argument contains text logical or empty cells; the values are ignored, and cells with the value zero are ignored.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In the end, in this article, we have tried to make the Excel SLOPE function easier for you. Download the practice workbook added to this article and practice yourself. If you feel any confusion, just comment in the box below.

Excel SLOPE Function: Knowledge Hub

• How to Find Slope of Tangent Line in Excel
• How to Find the Slope of a Regression Line in Excel
• How to Find Slope of Logarithmic Graph in Excel
• How to Calculate Slope of Exponential Graph in Excel

<< Go Back to Excel Functions | Learn Excel