How To Make A Residual Plot On Google Sheets

In this tutorial, you will learn how to make a residual plot on Google Sheets.

In statistics, a residual is a measure of how far a data point falls from the regression line. Residual values closer to zero mean the regression line (or line of best fit) is accurate for that data point, while residuals with greater absolute values show a less accurate fit

Residual plots make it easy to quickly get a better sense for how well a regression line fits a sample and identify outliers. This tutorial will show you how to make a residual plot in Google Sheets

Linear Regression

The first step is to do a linear regression on the sample. The resulting linear regression line will be used to calculate residuals. The equation for a linear regression line is in the form Y = a + bX where X is the independent variable and Y is the dependent variable. The letter b represents the slope of the line and the a represents the intercept, or the Y value when X =0, so we’ll need to find slope and intercept for the linear regression

For this example, let’s assume a company is trying to determine the correlation between the number of ads run and monthly sales, so our dependent variable will be sales and the independent variable will be ads.

Step 1

Enter your independent variables in column A and your dependent variables in column B

Step 2

Calculate the slope of the linear regression using this formula: “=SLOPE(B:B, A:A)”

Step 3

Calculate the intercept with this formula: “=INTERCEPT(B:B,A:A)”

Step 4

Now, we use the linear regression line formula to calculate the height of the line for each data point. The formula for the first row is: “=D$2*A2+D$4”. The slope times the x value plus the intercept. (Note the $ characters, which keep the references to the slope and intercept static when the formula is dragged down)

Step 5

Drag the linear regression formula down for all data points

Calculate Residuals

The formula to calculate the residuals is simply the difference between the Y (sales) value and the linear regression prediction we just calculated

Step 1

Here is the residual formula for the first row: “=B2-F2”

Step 2

Copy this formula down for all rows

Step 3

To make charting easier, insert a new column before the residuals and copy the X values from the first column into the new column

Adding the Plot

Now that the data is ready, we can add a residual plot

Here’s how:

Step 1

Residual plots plot the residuals against the original X values, so select the columns containing the X values (ads run) and the calculated residuals, then open the Insert menu and choose Chart

Step 2

In the Chart Editor, select the Scatter Plot chart type

Step 3

You have now added a residual plot to your spreadsheet

Step 4

When looking at your residual plot, you want to see a random arrangement of dots. If you see a pattern in the residual chart that means the linear regression model wasn’t a good fit for the data

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to make a residual plot on Google Sheets. Want more? Check out all the Google Sheets Tutorials.