It’s a numeric value that tells us how two variables are correlated. It tells us how much the dependent variable will change in case there is a change in the independent variable. There are three ways to find the slope of the regression line for a given set of variables in Excel:
Using the SLOPE FunctionUsing an Excel Scatter chart
In this tutorial, I show you how to calculate slope using each of the above three methods.
What is Slope? An Overview
A Slope is a value that tells us how two values (usually called the x and y values) are related to each other. To give you a simple example, if you have the data about the height and yearly income of some people and you calculate the slope for this data, it will tell you whether there is a positive or negative correlation between these data points. The slope value can be positive or negative. In our example, if the slope value is 138, which means that there is a positive correlation between height and the income of people. So if the height increases by 1 centimeter, the income is likely to increase by USD 138. Apart from the slope, another thing you need to know about is the Intercept. Let me explain it with the equation: In this equation, we have already calculated the slope, but to truly know what would be the Y value for a given X value, you also need to know the intercept. Thankfully, Excel has a formula for that as well, and I will cover how to calculate intercept in all the methods.
Method 1: Using the Excel SLOPE Function
The easiest way to calculate slope in Excel is to use the in-built SLOPE function. It finds the slope value of a given set of x-y coordinates in one step. While calculating slope manually could be hard, with the SLOPE function, you just need to give it the x and y values and it does all the heavy lifting in the backend.
SLOPE Function Syntax in Excel
The syntax for the slope function is: Here, y_vals and x_vals each consist of an array or range of cells containing numeric dependent data values. Remember that you need to give the Y values as the first argument and X values as the second argument. If you do it the other way round, you will still get the result but it would be incorrect. Suppose you have the below dataset as shown below where I have the height (in cm) as X values and average annual income (in USD) as the Y values. Below is the formula to calculate slope using this dataset: The above result tells me that from this dataset, I can assume that in case the height increases by 1 cm, the income would increase by USD 138.58. Another common statistical value that people often calculate when working with slope is to calculate the Intercept value. Just to refresh, the slope equation is something as shown below: While we know the slope, we would also need to know the intercept value to make sure we can calculate Y values for any X value. This can easily be done using the below formula: With this, our equation for this dataset becomes: So now, if I ask you what would be the income of anyone whose height is 165 cm, you can calculate the value easily. Both slope and intercept values can be positive or negative
Points to Remember when Using the SLOPE Function in Excel
Here are a few points to remember when finding the slope of a regression line using the SLOPE function:
Arguments of the SLOPE function have to be numerical (DATE values are also accepted). In case any of the cells are blank or contains a text string, these would be ignored In case there is ‘0’ in any cell/cells, it would be used in the calculation There should be an equal number of x and y values, when used as input for the SLOPE function. In case you give it unequal sized ranges, you will get a #N/A errorThere should be more than one set of points, otherwise the SLOPE function returns a #DIV! error
Method 2 – Using a Scatter Chart to get the Slope Value
If you prefer to visualize your data and the regression line, you can plot the data in a scatter chart and use it to find the slope and the intercept for the trend line (also called the line of best fit). Suppose you have the dataset as shown below and you want to find out the slope and intercept for this data: Below are the steps to do this: The above steps would insert a scatter chart that has a trendline, and the trendline also has the slope and intercept equation. In our example, we get the below equation: Here:
138.56 is the slope of the regression line65803 is the intercept of the regression line
You can compare this with the values we got from the SLOPE and INTERCEPT functions (it’s the same value). If the slope value is positive, you’ll see the trend line going up, and if the slope value is negative, then you will see the trend line going down. The steepness of the slope would be dependent on its slope value While the formula method to calculate slope and intercept is easy, the benefit of using the scatter chart method is that you can visually see the distribution of the data points as well as the slope of the regression line. And in case you are anyway creating a scatter chart for your data, getting the slope value by adding a trendline would actually be faster than using the formulas. So these are two really simple ways that you can use to calculate the slope and the intercept value of a data set in Excel. I hope you found this tutorial useful. Other excel tutorials you may also like:
How to Make a Bell Curve in Excel (Step-by-step Guide)How to Add Error Bars in Excel (Horizontal/Vertical/Custom)How to Calculate Standard Deviation in ExcelHow to Calculate Correlation Coefficient in ExcelCalculate Area Under Curve in an Excel
