We all know how excel can be used to visualize data through graphs and charts. The software can also be used to visualize errors and forecasts. The line that forecasts in excel is called a trendline.
Trendlines are used to forecast a certain phenomenon by evaluating a historical dataset. There is another use of the trendline, you can use your expected value of the independent variable and get an assumed or estimated dependent variable.
For example, you expect that the sales of ice cream will go down in winter by 30%, you can use that estimation to get the expected revenue from a trendline.
A trendline can also be used to determine the relationship between the dependent and independent variables.
That relationship can be determined by the slope of the trendline.
So, today let’s learn how to find slope of trendline in excel.
What is slope?
Slope measures the change of a dependent variable in respect to the change of an independent variable. In other words, the slope is the measure of steepness.
The formula is:
Slope = change in y/change in x = rise/run
For example, if an equation is y=200x+50. We can compare it to the linear equation of y=mx+c and find m to be 200 which is the slope.
So, let’s say if the price of a juice bottle increases by $1 the demand increases by 200 bottles.
The use of slope is deeply embedded into the dynamics of economics.
Now, let’s look at how to find slope of the trendline in excel.
There are two to find slope in excel. Let’s look at them one by one.
Method#1 Calculating Slope Through a Trendline
First, you have to create a data set that contains both dependent variables or y and independent variables or x.
Now, it’s time to create the graph. Go to the Insert ribbon and from the Recommended Charts select a scatter chart.
Then to add a trendline, go to Chart Elements, or from the green + sign at the top right corner of the graph, select a linear trendline.
Now, double click on the trendline that has been created on the graph and you will see a Format Trendline options pop up on the right hand side.
Form those options, tick the Display Equation on Chart option, You will find that option at the bottom.
You will get an equation inside of the graph. If you notice the equation, you can compare it with the y=mx+c linear equation.
y = -1.9558x + 39.606 this is the equation you should get if you create the trendline according to the data set in the picture.
Here, the m or the slope is -1.9558 which shows that the dependent and the independent variables are negatively related.
You can also calculate the slope of the scatter plot itself by a simple formula.
Method#2 Calculating Slope Through the SLOPE Formula
In any cell type the following formula
=SLOPE(F7:F27,G7:G27)
You will get a slope of -0.50828. You may wonder why it does not match with the slope of the trendline.
It doesn’t match because the trendline was linear and the scatter plot is not linear.
So, different lines have different slopes.
Conclusion
The slope just helps to estimate based on historical data. It is not accurate but it is a very useful tool to use in business and economics to counter uncertainty and forecast.
So, there you go now you also know how to find slope of trendline in excel.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.