How To Create Linear Regression in MS Excel

188

In this article we will show you how To Create Linear Regression in MS Excel. Linear regression is a statistical strategy used to find out how two continuous quantitative variables are related to each other. In this method, the value of a dependent variable is guessed based on the values of other variables.

Simple linear regression is when there is only one independent variable, and multiple linear regression is when there are more than one independent variables. Linear regression models show how a dependent variable and an independent variable relate to each other by fitting a linear equation to the data. Linear means that we fit our data by drawing a line.

In regression analysis, the dependent variables are also called the response variables or the variables that are predicted. The independent variables are also called the explanatory variables or the variables that are predicted. You are given a bunch of different information and asked to guess how much your company will sell next year.

Ways To Create Linear Regression in MS Excel

Adding a Linear Regression Trendline to Graph

1. First, open a blank Excel spreadsheet, select cell D3 and enter ‘Month’ as the column heading, which will be the x variable.
2. Then click cell E3 and input ‘Y Value’ as the y variable column heading. This is basically a table with a recorded series of data values for the months Jan-May.
3. Enter the months in cells D4 to D8 and data values for them in cells E4 to E8.

You can now put together a scatter graph for that table.

1. Select all the cells in the table with the cursor.
2. Click the Insert tab and select Scatter > Scatter with only Markers to add the graph to the spreadsheet as below. Alternatively, you can press the Alt + F1 hotkey to insert a bar graph.
3. Then you should right-click the chart and select Change Chart Type > X Y (Scatter) > Scatter with only Markers.

The trend line can then be added to the scatter plot.

1. Select one of the data points on the scatter plot and right-click to open the context menu, which includes an Add Trendline option.
2. Select Add Trendline to open the window. That window has five tabs that include various formatting options for linear regression trendlines.
3. Click Trendline Options and select a regression type from there. You can select Exponential, Linear, Logarithmic, Moving Average, Power and Polynomial regression type options from there.
4. Select Linear and click Close to add that trendline to the graph.

Formatting the Linear Regression Trendline

Formatting the linear regression trendline is a key part of making graphs in Excel that are easy to read.

1. To start formatting the trendline, you should right-click it and select Format Trendline.
2. That will open the Format Trendline window again from which you can click Line Color.
3. Select Solid line and click the Color box to open a palette from which you can choose an alternative color for the trendline.
4. To customize the line style, click the Line Style tab. Then you can adjust the arrow width and configure the arrow settings.
5. Press the Arrow settings buttons to add arrows to the line.

You can also make your trendline look better by adding effects.

1. Add a glow effect to the trendline by clicking Glow and Soft Edges. That will open the tab below from which you can add glow by clicking the Presets button.
2. Then select a glow variation to choose an effect. Click Color to select alternative colors for the effect, and you can drag the Size and Transparency bars to further configure the trendline glow.

Forecasting Values with Linear Regression

Once you’ve set up the trendline, you can also use it to predict what the values will be in the future. For example, let’s say you need to predict a data value for August, which is three months after May but not in our table. You can also go to the official Microsoft Excel support site to know more information.

1. Click Trendline Options and enter ‘3’ in the Forward text box.
2. The linear regression trendline highlights that August’s value will probably.

You can add the equation and r square value for each linear regression trendline to the chart.

1. Click the Display Equation on chart check box to add the equation to the graph. That equation includes a slope and intercept value.
2. To add the r square value to the graph, click the Display R-squared value on chart check box. That adds r squared to the graph just below the equation as in the snapshot below.
3. Drag the equation and correlation box to alter its position on the scatter plot.

What Is Linear Regression?

Linear regression is a way to look at data that looks at how a dependent variable is related to one or more independent variables in a straight line. It is usually used to show visually how strong the relationship or correlation is between different factors and how spread out the results are.

This is done so that the behavior of the dependent variable may be explained. A linear regression model tries to figure out how strong a link between two variables is and if it is statistically significant. Say we wanted to find out how strong the link is between how much ice cream people eat and their weight.

We would look at the amount of ice cream as the independent variable and compare it to obesity as the dependent variable to determine whether there was a link. Given that a regression is a graphical representation of this relationship, the stronger the link and the better the fit to the regression line, the less the data vary from one another.