Last Updated on November 3, 2023 by Jake Sheridan

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.

#### More ways to make graphs & charts:

- Use Slicer
- Line Chart
- Combo Chart
- Area Chart
- Column Chart
- Bar Char
- Boxplot
- Bubble Chart
- Pareto Chart
- Quadrant Chart
- Scatter Plot With Lines
- Stacked Bar Chart
- Pie Chart
- Scatter Chart
- Histogram Chart
- Candlestick Chart
- Organisational Chart
- Progress Bars
- Dot Plot
- Gauge Chart
- Bell Curve
- Double Bar Graph
- Add Average Line To Chart
- Add A Second Y-Axis
- Add Axis Labels
- Plot Multiple Lines
- Create A Table

- Add Error Bars To Charts
- Add Labels To Scatterplot Points
- Add Equation To Graph
- Change Chart Colors
- Copy Graphs
- Label A Legend
- Label Legend
- Box And Whisker Plot
- Dot Plot
- Gantt Chart
- How To Make A Chart
- Line Graph With Multiple Lines
- Organizational Chart
- Put Two Sets Of Data On One Graph
- Add A Horizontal Line To A Chart
- Add Trendline To Chart
- Add Multiple Trendlines To Chart
- Chart With Multiple Ranges Of Data
- Line Of Best Fit
- Standard Deviation Bars
- Multiple Trendlines
- Add A Y Axis
- Data Labels
- Switch X And Y Axis
- Change Font
- Smooth Line Chart

- Stacked Area Chart
- 100% Stacked Area Chart
- Stepped Area Chart
- Stacked Stepped Area Chart
- 100% Stacked Stepped Area Chart
- Stacked Column Chart
- 100% Stacked Column Chart
- 100% Stacked Bar Chart
- Doughnut Chart
- 3D Pie Chart
- Scatter Chart
- Geo Chart
- Geo Chart With Markers
- Waterfall Chart
- Radar Chart
- Scorecard Chart
- Treemap Chart
- Timeline Chart
- Table Chart
- Rotate Data Tables
- Combine Two Graphs
- Add Series
- Change Series Name
- Plot Multiple Lines on a Chart
- How To Analyze Data