How to Perform Linear Regression in Google Sheet

How to Perform Linear Regression in Google Sheet
Reading Time: 3 minutes

You’re going to learn how to create a graphical representation of Linear Regression using Google Sheets. Formulate linear relationships between variables.

Why use Linear Regression?

Linear regression helps in predicting the value of a variable based on the value of the other variable if a linear relationship exists between them. In this article, you will go through how you can perform linear regression using Google sheets. But before jumping in, the dependent variable is on the Y-axis and the independent variable on the X-axis. 

  • Follow the steps as described in this article to get a graphical representation along with the equation, as shown below:
Linear regression graph for Profits vs Year.
Figure 1: Linear regression graph for Profits vs Year. You can see the linear equation and the trendline in the graph

Steps to Perform Linear Regression

Step 1:- Prepare and select data

  • To perform regression, you need to have a dataset with a dependent variable (here Profits) and an independent variable (here Year). 
  • Select the dataset you want to perform linear regression on.
  • Left-click on cell A1 and drag it down to B23 along with the titles of the variable columns as shown below:
Cell selection to perform linear regression
Figure 2: Cells are selected to perform linear regression

Step 2:- Insert Chart

  • Now, click on the Insert tab from the navigation bar.
  • Select the Chart option to create a graphical representation of the regression.
Insert Chart to perform Linear Regression
Figure 3: Select the Chart option from the Insert bar

Step 3:- Create a scatter plot

  • Now, navigate to the Chart type drop-down menu.
  • Select Scatter chart as the chart type and select the Scatter plot, as shown below :
Selecting scatter plot from scatter chart to plot data points.
Figure 4: Selecting scatter plot from scatter chart to visualize individual data points on the plot

Step 4:- Customize the graph settings to visualize

  • To plot the trend line, select the Customize tab
  • Go to the Series tab for plotting the trend line :
Scattered data points
Figure 5: The graph above shows the scattered data points which were done in the previous step

Step 5:- Plot trendline, equation of graph, and method used for regression analysis

  • Checkmark the Trendline option to see the trend line of the points as seen in the graph below.
  • Navigate to the Label drop-down menu
  • Select the Use Equation option to display the equation of the graph.
  • Tick mark the Show R2 checkbox to demonstrate the R2 value of the graph.
  • The higher the R2 value, the higher is correctness to predict any value and fit to the model.
Selecting Trend line and Equation for Linear Regression.
Figure 6: Select the highlighted checkboxes and drop-down menu to have the best view of the regression model.

See Also

Plot Multiple Data Ranges on a Single Chart: To plot multiple data ranges on a single graph along with their equations of regression.

Eliminate Duplicates Using Queries in Google Sheets: Learn how to use the Google Sheets Query formula to remove duplicates from the dataset.

References

  1. https://scikit-learn.org/stable/modules/generated/sklearn.linear_model.LinearRegression.html
  2. https://mathcs.holycross.edu/~little/Mont201920/RegDirs.pdf