Excel Linear regression is one of the statistical concepts that must be mastered when designing predictive analyses and machine learning models.
While this is one of the mathematical basics you need to know, calculating it can be more or less complex. Fortunately, there are various tools available to make your predictions much more reliable.
This is particularly true of linear regression calculations in Excel. So how do you go about it?
DataScientest explains all the steps you need to take.
💡Related articles:
What is Ecel linear regression?
Before we look at how linear regression works in Excel, we need to understand linear regression. A quick reminder is in order!
In practical terms, linear regression is used to analyse the relationship between an independent variable (or explanatory variable) and a dependent variable (or target variable). This statistical technique is expressed as follows:
Y = aX + b
where :
Y= the dependent variable you are trying to predict.
X= the independent variable that explains Y.
a = the slope coefficient which represents how Y changes as a function of X.
b = the y-intercept, which is the value of Y when X is zero.
The aim is to create a model that can be used to make predictions about the dependent variable based on the values of the explanatory variable.
While Excel linear regression can always be used to explain a dependent variable, it can use a single independent variable (simple linear regression) or several independent variables (multiple linear regression).
How do I use Excel linear regression?
There are several functions for calculating linear regression in Excel. They allow you to calculate either multiple regression, simple regression or both.
The RightReg function
The matrix function DroiteReg (or Linest in English) can be used to calculate both simple linear regression and multiple linear regression in Excel.
Here’s how to do it:
- Identify your dependent values Y and your independent values X. These must be in two separate columns.
- Click on the cell where you want to display the results of the linear regression with Excel. This may be a new worksheet or a range of empty cells.
- Enter the formula “=RightReg(Y, X)” where “Y” corresponds to the range of cells containing the values of the dependent variable (for example, rows 2 to 17 in column A) and “X” corresponds to the range of cells containing the values of the independent variable (for example, rows 2 to 17 in column B).
- To repeat the example, you would type the formula “=RightReg(A2:A17, B2:B17)”.
- Click on “Enter”.
Excel automatically calculates the linear regression and displays the results in the selected cells. You will then see two values appear: the regression coefficient (the slope) and the y-intercept.
If you want to calculate a multiple linear regression with Excel, you can also use this formula. In this case, simply add the other columns containing the values of the dependent variable, separating them by commas. For example, if your cell range A can be explained by the values contained in columns B, C and D, your formula will look like this: “=RightReg(A2:A17,B2:B17,C2:C17,D2:D17)”.
The Trend function
The Trend function can be used to calculate simple linear regression in Excel. In this case, the operation is similar to that of the StraightReg function. Note :
- Click on the cell where you want to display the results of the linear regression with Excel.
- Enter the formula “=Trend(A2:A17, B2:B17)”.
- Click on “Enter”.
Although the formula is almost identical to the previous one, the result is different. The trend function does not isolate the slope and y-intercept parameters.
The Excel linear regression option
The Data Analysis option available in your Excel spreadsheet allows you to obtain complete information for linear regression. Here are the steps to follow:
- Go to the “Data” tab;
- Click on “Data analysis” in the “Analysis” group.
Select the linear regression. - A “Data analysis” dialogue box appears. Choose “Regression” from the available tools and enter the required parameters:
- In the “Dependent variable” box, enter the cell containing your dependent variable (Y).
- In the “Independent variable(s)” field, enter the cells containing your independent variables (X). If you have several independent variables, separate them with commas.
- Check the ‘Regression output’ box to obtain additional information about the analysis, such as residuals, fit statistics, etc.
- Click on OK.
Excel then performs the linear regression analysis and displays the results in the worksheet or range of cells specified. This option displays a range of additional information, such as regression coefficients, slope and y-intercept values, fit statistics, t-values, p-values and so on.
Graphing linear regression with Excel
It is also possible to represent the linear model graphically using Excel. Here are the steps to follow:
- Select all your data;
- Click on “Insert”, then “Chart”;
- On the scatter plot, click on “Chart tools”, then “Add an element to the chart” to display the axis titles, the chart title and a legend.
- To plot the linear line, right-click on the curve and click on “add a trend line”.
- Select “linear”, then tick “display equation”, then “display coefficient of determination”.
Master Excel with DataScientest
Microsoft Excel is the essential tool for analysing data. It enables you to perform a range of calculations, such as simple or multiple linear regression…
And if you want to develop complex Machine Learning models, you will also need to master programming languages such as R, Python… And it’s precisely through our data science training course that you will be able to master all the tools that are essential for predictive data analysis and machine learning.
