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 among 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.


Download Practice Workbook

You can download the practice workbook from here.


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
ARGUMENT REQUIREMENT EXPLANATION
known_y’s Required An array or cell range of numeric dependent data points.
known_x’s Required The set of independent data points.
  • 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 is used to determine the slope of a linear regression line is given below:Overview of Excel SLOPE Function

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

Read More: How to Use AVERAGE Function in Excel (5 Examples)


5 Uses of SLOPE Function in Excel

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.

Calculate Slope of a Regression Line Using Excel Slope Function

  • First, select cell C11. Insert the following formula and press Enter.
=SLOPE(C5:C9,B5:B9)

Calculate Slope of a Regression Line Using Excel Slope Function

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

Calculate Slope of a Regression Line Using Excel Slope Function

  • 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.

Calculate Slope of a Regression Line Using Excel Slope Function

  • Here we will get a graph like this.

Calculate Slope of a Regression Line Using Excel Slope FunctionHere, 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”.

Calculate Slope of a Regression Line Using Excel Slope Function

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

Calculate Slope of a Regression Line Using Excel Slope Function

  • 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 name 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.

Use of Excel SLOPE Function to Calculate Negative Slope

  • Firstly, select cell C11 and insert the following formula:
=SLOPE(B5:B9,C5:C9)
  • Then press Enter. We will see a negative value of slope in cell C11.

Use of Excel SLOPE Function to Calculate Negative Slope

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


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:
=INTERCEPT(C5:C9,B5:B9)
  • Next, press Enter. We will get the value of the intercept part in cell C12.

Pairing SLOPE and INTERCEPT Functions Together

  • 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 Use INTERCEPT Function in Excel


Similar Readings


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.

Use of Excel SLOPE Function as VBA Function

It should be noted that if we do not have the Developer tab in our Excel we have to activate the Developer tab 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.

Use of Excel SLOPE Function as VBA Function

  • Next, select the Options.

Use of Excel SLOPE Function as VBA Function

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

Use of Excel SLOPE Function as VBA Function

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

Use of Excel SLOPE Function as VBA Function

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

Use of Excel SLOPE Function as VBA Function

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.


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.

#N/A! Error While Using Excel SLOPE Function

  • Firstly, we will select cell C10 and insert the following formula:
=SLOPE(C5:C9,B5:B10)

#N/A! Error While Using Excel SLOPE Function

  • 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.

Excel SLOPE Function giving  #DIV/0! Error

  • Select cell C10 and insert the following formula:
=SLOPE(C5:C9,B5:B9)

Excel SLOPE Function giving  #DIV/0! Error

  • 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 to 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 cells with the value zero are ignored.


Conclusion

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


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo