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
Quick Navigation
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.