How To Find Slope Of Line In Excel

The slope of the regression line is a measure out of the steepness of the line.

It'due south a numeric value that tells usa how ii variables are correlated. It tells us how much the dependent variable will change in instance in that location is a change in the contained variable.

At that place are three means to detect the slope of the regression line for a given fix of variables in Excel:

Using the Slope Function
Using an Excel Besprinkle chart

In this tutorial, I show you how to calculate slope using each of the above iii methods.

What is Slope? An Overview

A Gradient is a value that tells us how two values (ordinarily called the x and y values) are related to each other.

To give you lot a simple example, if you have the information virtually the meridian and yearly income of some people and you lot calculate the slope for this data, it will tell y'all whether in that location is a positive or negative correlation between these information points.

The slope value can be positive or negative.

In our instance, if the slope value is 138, which ways that there is a positive correlation between height and the income of people. So if the height increases past one centimeter, the income is probable to increase by USD 138.

Autonomously from the slope, another affair yous demand to know about is the Intercept.

Let me explain it with the equation:

Y = Slope*Ten + Intercept

In this equation, we take already calculated the slope, but to truly know what would be the Y value for a given X value, you as well demand to know the intercept.

Thankfully, Excel has a formula for that likewise, and I will cover how to calculate intercept in all the methods.

Method 1: Using the Excel Gradient Role

The easiest mode to summate slope in Excel is to utilise the in-built Slope role.

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 part, 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:

=Slope(y_vals, x_vals)

Hither, y_vals and x_vals each consist of an array or range of cells containing numeric dependent data values.

Call up that you demand to give the Y values as the first argument and X values as the second argument. If y'all do it the other style round, you will still get the result simply it would be incorrect.

Suppose you take the beneath dataset as shown below where I have the height (in cm) equally Ten values and average almanac income (in USD) as the Y values.

Dataset to calculate slope and interceot

Below is the formula to calculate gradient using this dataset:

=Gradient(B2:B11,A2:A11)

The in a higher place consequence tells me that from this dataset, I tin can assume that in case the peak increases past 1 cm, the income would increase by USD 138.58.

Another common statistical value that people ofttimes calculate when working with slope is to summate the Intercept value.

Just to refresh, the slope equation is something as shown below:

          Y = Slope*Ten + Intercept

While we know the slope, we would also need to know the intercept value to make sure we can calculate Y values for any Ten value.

This can easily be done using the below formula:

=INTERCEPT(B2:B11,A2:A11)

With this, our equation for this dataset becomes:

Y = 138.56*10 + 65803.2

And so now, if I ask y'all what would be the income of anyone whose summit is 165 cm, you can summate the value easily.

Y = 138.56*165 + 65803.2

Both gradient and intercept values can be positive or negative

Points to Remember when Using the Gradient Function in Excel

Here are a few points to recollect when finding the slope of a regression line using the Gradient function:

Arguments of the Slope office accept to exist numerical (DATE values are also accepted). In case any of the cells are bare or contains a text string, these would be ignored
- In case there is '0' in whatever cell/cells, it would be used in the calculation
There should exist an equal number of ten and y values, when used as input for the Slope function. In instance you lot requite it diff sized ranges, you will get a #North/A mistake
In that location should be more than i gear up of points, otherwise the Gradient function returns a #DIV! mistake

Method two – Using a Scatter Chart to get the Slope Value

If y'all prefer to visualize your information and the regression line, y'all can plot the data in a scatter nautical chart and use it to find the slope and the intercept for the trend line (besides called the line of best fit).

Suppose you have the dataset as shown beneath and you want to find out the gradient and intercept for this data:

Dataset to calculate slope and intercept using scatter plot

Below are the steps to do this:

Select both X and Y data points (in our example, it would exist the height and income column)
Click on the 'Insert' tab in the ribbon

Click on the 'Insert scatter' dropdown (under the Charts group)
From the dropdown that appears, select the 'Scatter nautical chart' option

This will insert a scatter nautical chart into your worksheet, displaying your x-y values as scatter points (as shown beneath)

Right-click on one of the scatter points, and select 'Add together Trendline' from the context menu that appears. This will insert the trendline and as well open the 'Format Trendline' pane in the correct

In the Format Trendline pane, inside the 'Trendline Options', select the 'Display Equation on chart' checkbox

Select display equation on the chart option

Shut th Format Trendline pane

The above steps would insert a besprinkle nautical chart that has a trendline, and the trendline too has the slope and intercept equation.

Slope and Intercept equation in the chart

In our example, we get the beneath equation:

y = 138.56x + 65803

Here:

138.56 is the gradient of the regression line
65803 is the intercept of the regression line

You tin compare this with the values nosotros got from the SLOPE and INTERCEPT functions (information technology's the aforementioned value).

If the slope value is positive, yous'll see the trend line going upwardly, 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 gradient value

While the formula method to calculate slope and intercept is easy, the benefit of using the scatter chart method is that y'all tin visually come across the distribution of the information points as well as the slope of the regression line.

And in instance you are anyway creating a scatter nautical chart for your data, getting the slope value past adding a trendline would actually exist faster than using the formulas.

Then these are two really uncomplicated ways that yous can employ to calculate the slope and the intercept value of a data set in Excel.

I hope y'all plant this tutorial useful.

Other excel tutorials you may too like: