How To Create Linear Regression in MS Excel 2021 February

The How To Create Linear Regression in MS Excel

Linear regression models a relationship between the dependent y and independent x statistical data variables. In other words, they highlight a trend between two table columns on a worksheet. For example, if you set up an Excel worksheet table with a month x column and save a data set for each of the months in the adjacent y column, the linear regression highlights the trend between the x and y variables by appending from trendlines to chart charts. This is how you can add linear regression to Excel charts.

Adding a linear regression trendline to the graph

First, open a blank Excel worksheet, select cell D3, and enter “Month” as the column header, which will be the variable x. Then click on cell E3 and enter “Y Value” as the column header of variable y. It is basically a table with a recorded series of data values ​​for the months of January through May. So enter the months into cells D4 through D8 and their data values ​​into cells E4 through E8 as shown in the snapshot below.

You can now configure a point cloud for this table. Select all the cells of the table with the cursor. Click on the Insert tab and select Scatter> Scatter with only markers to add the chart to the worksheet as below. You can also press the Alt + F1 hotkey to insert a bar graph. Then you need to right click on the graph and select Change Graph Type> XY (Scatter)> Scatter with Markers Only.

Then select one of the data points on the point cloud and right click to open the context menu, which includes an Add Trendline option. Select Add Trendline to open the window shown in the snapshot below. This window has five tabs that include various formatting options for linear regression trendlines.

First, click on 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. Select Linear and click Close to add this trendline to the chart as shown below.

The linear regression trendline in the graph above highlights that there is a general upward relationship between the x and y variables despite some declines on the graph. Note that the linear regression trendline does not overlap any of the data points on the graph, so it is not the same as your average line graph that connects each point.

Formatting the Linear Regression Trendline

To format the trendline, you need to right-click on it and select Format Trendline. This will reopen the Format Trendline window from which you can click Line Color. Select Solid Line and click the Color box to open a palette where you can choose an alternative color for the trendline.

To customize the line style, click the Line Style tab. Then you can adjust the width of the boom and configure the boom settings. Press the arrow adjustment buttons to add arrows to the row.

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

Predicting values ​​with linear regression

Once you have formatted the trendline, you can also forecast future values ​​with it. For example, suppose you need to forecast a data value three months after May for August, which is not included in our table. Then you can click Trendline Options and enter “3” in the Transfer text box. The linear regression trendline highlights that the August value will likely be just above 3500, as shown below.

Each linear regression trendline has its own equation and square value that you can add to the graph. Select the Show equation on graph check box to add the equation to the graph. This equation includes a slope and an intercept value.

To add r-squared value to the graph, select the Show R-squared value on graph check box. This adds r squared to the graph just below the equation like in the snapshot below. You can drag the equation and the correlation box to change its position on the point cloud.

Linear regression functions

Excel also includes linear regression functions with which you can find slope, intercept, and r-squared values ​​with for y and x data arrays. Select a worksheet cell to add one of these functions to, and then press the Insert Function button. Linear regression functions are statistical, so select Statistic from the category drop-down menu. Then you can select RSQ, SLOPE or INTERCEPT to open their function windows as below.

The RSQ, SLOPE and INTERCEPT windows are roughly the same. They include the Known_y’s and Known_x boxes that you can select to add the values ​​of the y and x variables from your table. Note that cells should only include numbers, so replace the months in the table with the corresponding numbers such as 1 for January, 2 for February, etc. Then click OK to close the window and add the function to the worksheet.

Now you can beautify your Excel worksheet charts with linear regression trendlines. They will highlight general trends in graph data points, and along with regression equations, they are handy forecasting tools as well.

Benefits: How To Create Linear Regression in MS Excel

FAQ: How To Create Linear Regression in MS Excel

Blog Conclusion: How To Create Linear Regression in MS Excel