In this article:

How to Do Linear Regression in Google Sheets (2024 Update)

Linear Regression in Google Sheets

Linear regression is a statistical method used to model the relationship between a dependent variable and one or more independent variables by fitting a linear equation to observed data. In Google Sheets, you can perform linear regression analysis to understand how changes in independent variables are associated with changes in a dependent variable.

Google Sheets Linear Regression Syntax

To perform linear regression in Google Sheets, you primarily use the LINEST function, which provides the slope and intercept of the linear regression line, along with other statistics that can help you understand the strength and direction of the relationship.

The syntax for the LINEST function, which is used to perform linear regression in Google Sheets, is as follows:

LINEST(known_y's, known_x's, [const], [stats])

Here's what each part of the syntax represents:

  • known_y's: This is the range of cells that contain the dependent variable (the variable you are trying to predict).

  • known_x's: This is the range of cells that contain the independent variable(s) (the variable(s) you are using to predict the dependent variable). For multiple regression, include multiple ranges separated by commas or use a single range that spans multiple columns.

  • [const]: This is an optional argument. If TRUE or omitted, the function calculates the y-intercept (b) of the line. If FALSE, the y-intercept is set to zero, and the line will be forced through the origin.

  • [stats]: This is another optional argument. If TRUE, the function returns additional regression statistics, such as the standard error of the estimate, R-squared value, etc., in an array. If FALSE or omitted, the function returns only the coefficients (slope and y-intercept) of the regression line.

How to Do Simple Linear Regression in Google Sheets

Simple linear regression is a statistical method used to understand and quantify the relationship between two variables: one independent (X) and one dependent (Y). 

Follow the steps below to run a simple linear regression in Google Sheets. 

1. Input Data into Google Sheets

Start by inputting your data into Google Sheets. For this example, place your independent variable, the advertising budget (X), in column B, and your dependent variable, monthly sales (Y), in column D. This organization is crucial for the LINEST function to accurately interpret your data. Label the top of these columns as "Advertising Budget" and "Monthly Sales" for clarity.

linear regression google sheets

2. Choose a Cell for Linear Regression Slope Output

Choose a cell where you want the linear regression output to appear, such as E2 for the slope. This selection tells Google Sheets where to display the result of the LINEST function, which calculates the relationship between your advertising budget and monthly sales.

google sheets linear regression

3. Apply LINEST Function to Calculate Slope and Y-Intercept

Type =LINEST(D2:D13, B2:B13) directly into the formula bar, ensuring you replace D2:D13 with the range that matches your monthly sales data and B2:B13 with your advertising budget range.

linear regression in google sheets

This formula will calculate the slope of the regression line, which represents the average increase in monthly sales for each thousand dollars spent on advertising. This will also show the y-intercept value which tells you the expected monthly sales when the advertising budget is zero.

how to do linear regression in google sheets

4. Interpret Results

Slope: Approximately 3.08

Intercept: Approximately 25.91

These results mean that for every $1,000 increase in the advertising budget, the monthly sales are expected to increase by about $3,080. The intercept indicates that if there were no spending on advertising, the monthly sales would still amount to approximately $25,910.

5. Visualize Relationship with Scatter Plot and Trendline

Highlight both your advertising budget and monthly sales data, then insert a scatter plot through Insert > Chart.

linear regression sheets

Customize the chart by selecting the scatter plot option. This visual representation helps you see the linear relationship between advertising spend and sales revenue.

linear regression on google sheets

How to Do Multiple Linear Regression in Google Sheets\

Multiple linear regression extends simple linear regression to include two or more independent variables (X1, X2, ...) predicting a single dependent variable (Y). This method allows for the examination of the combined effect of several variables on the outcome, providing a more complex and detailed analysis. 

Follow the steps below to run multiple linear regression in Google Sheets. 

1. Organize Dataset for Multiple Linear Regression Analysis

Input your dataset into Google Sheets with monthly sales (Y) in column D, advertising budget (X1) in column B, and sales calls (X2) in column C. This setup allows you to analyze how both advertising budget and sales calls together affect monthly sales. Label the tops of these columns for easy reference.

linear regression in sheets

2. Select a Range for Multiple Regression Output

Choose a range of cells, like E2:H5, to display the output from the LINEST function. This area needs to be large enough to accommodate the regression coefficients and any additional statistics, like the R-squared value, which measures the fit of your regression model.

3. Calculate Coefficients Using LINEST for Multiple Variables

Type =LINEST(D2:D13, B2:C13, TRUE, TRUE) in the formula bar and press Enter.

This command tells Google Sheets to calculate the coefficients for both your independent variables (advertising budget and sales calls) in relation to monthly sales. It will also provide additional regression statistics because of the TRUE parameters.

4. Analyze Coefficients and Model Fit

Examine the output. The first row shows how much monthly sales are expected to increase with each additional thousand dollars spent on advertising and with each additional sales call. Subsequent rows give you statistical insights, such as the model's overall fit (R-squared value), helping you understand the effectiveness of your sales efforts.

Intercept: Approximately 24.34

Coefficient for Advertising Budget: Approximately 2.01

Coefficient for Sales Calls: Approximately 0.55

In the context of multiple linear regression:

  • The intercept of around 24.34 suggests that with no spending on advertising and no sales calls, the monthly sales would be approximately $24,340.

  • The coefficient for the advertising budget of about 2.01 means that, holding sales calls constant, for every $1,000 increase in the advertising budget, monthly sales are expected to increase by about $2,010.

  • The coefficient for sales calls of about 0.55 indicates that, holding the advertising budget constant, for each additional sales call, monthly sales are expected to increase by approximately $550.

We hope that you now have a better understanding of how to do linear regression in Google Sheets. If you enjoyed this article, you might also like our article on how to remove all hyperlinks in Google Sheets or our article on why the Google Sheets app keeps crashing.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started