How To Create A Linear Regression In Excel
How to do Linear Regression in Excel
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
One of the best approaches in analyzing quantitive data in Excel is by running a simple linear regression.
With the latest versions of Excel, it doesn't take more than a minute to make one.
However, this wasn't always the case. Previously, you have to use Excel's Analysis ToolPak or a combination of different functions.
Kasper Langmann, Co-founder of Spreadsheeto In this article, let's talk about the basics of simple linear regression in Excel and then we'll show you how to make one fast and easy.
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
The Simple Linear Regression
Linear regression analysis, in general, is a statistical method that shows or predicts the relationship between two variables or factors .
There are 2 types of factors in regression analysis:
- Dependent variable (y) : It's also called the 'criterion variable' , 'response' , or 'outcome' and is the factor being solved.
- Independent variable (x): This is otherwise known as 'explanatory variables' or 'predictors'. They are factors used in solving the dependent variable due to their influence or effect on the said variable.
Usually, this type of analysis is used when one is trying to find or establish the correlation between variables .
Kasper Langmann, Co-founder of Spreadsheeto Here's the linear regression formula :
y = bx + a + ε
As you can see, the equation shows how y is related to x .
On an Excel chart, there's a trendline you can see which illustrates the regression line — the rate of change .
Here's a more detailed definition of the formula's parameters:
- y (dependent variable)
- b (the slope of the regression line)
- x (independent variable)
- a (y-intercept of the regression line)
- ε (the error term which accounts the variability in y that can't be explained by the analysis)
The analysis accounts for an error since they can't be completely eliminated especially in a predictive analysis such as this.
But don't be surprised if you can't find the error term in Excel. The program does it in the background.
Now, let's proceed into making one in Excel!
Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I've included one for you (for free).
Download it right below!
Download the FREE Exercise File
Simple linear regression in Excel
The first part of making a simple linear regression graph in Excel is making a scatter plot .
For convenience, let's use the same data set with the scatter plot exercise. Let's assume you're visualizing your e-commerce site's pageviews and sales the previous year.
In summary, here's what you need to do to insert a scatter plot in Excel:
- Format your data in such a way that the independent variable is on the left column and the dependent variable on the right .
- Highlight your data .
- Find and click the 'Scatter' icon under the 'Scatter' group on the 'Charts' category on the ribbon.
There's your scatter plot!
For convenience, edit the chart (bounds and titles) so the data points look centered on the chart.
If you're not sure what to do, feel free to refer to the 'scatter plot' article.
Kasper Langmann, Co-founder of Spreadsheeto 
Once you got it, let's now head over to the main part.
To draw the regression line , let's add a trendline on the chart.
Click on any of the data points and right-click . Select 'Add Trendline' .
After that, a window will open at the right-hand side.
'Linear' is the default 'Trendline Options' . If it's not selected, click on it .
Also, if you like to show the equation on the chart , tick the 'Display Equation on chart' box .
Once you do so, the equation will show on the chart. Feel free to move it around so that it doesn't block the data points or the line .
If you like to edit the look of the trendline , just right-click on it and select 'Format Trendline' .
This will open the same window as earlier. Where you need to go, however, is the 'Fill & Line' tab.
In here, you can edit the line's color, transparency, width, dash type, and others .
All in less than a minute. Fast and easy! 👍
How to interpret the results
Primarily, what you're looking in a simple linear regression is the correlation between the variables . Fortunately, in Excel, the trendline does it all for you.
The trendline will tell you if the relationship of your variables is positive or negative.
- Positive: If the line shows an upward trend . This indicates that as the independent variable increases, the dependent variable also increases . The same with our example, as the pageviews increase, we can expect to see a rise in sales as well.
- Negative: If the line shows a downward trend . This suggests that as the independent variable increases, the dependent variable decreases .
- None at all: This is easy to spot. There is no correlation between the variables (therefore, no way to predict the next values) when the points in the scatter plot don't resemble a line as they are scattered. You can still see a line if you add a trendline no matter how random the points are, but the line is usually close to a horizontal line.
Wrapping things up…
The simple linear regression is a good tool to determine the correlation between two or more variables. Before, you have to mathematically solve it and manually draw a line closest to the data.
It's a good thing that Excel added this functionality with scatter plots in the 2016 version along with 5 new different charts . Now, you can visually see the correlation between the variables without breaking a sweat.
Kasper Langmann, Co-founder of Spreadsheeto How To Create A Linear Regression In Excel
Source: https://spreadsheeto.com/linear-regression-excel/
Posted by: engelhardtbusert.blogspot.com
0 Response to "How To Create A Linear Regression In Excel"
Post a Comment