Return to Charts Home

Calculate & Plot Residuals – Excel & Google Sheets

Written by

Reviewed by

Steve Rynearson

Last updated on October 30, 2023

This tutorial will demonstrate how to calculate and plot residuals in Excel and Google Sheets.

Calculate & Plot Residuals – Excel

Starting with your Data

We’ll start with this dataset containing values for the X and Y Axis.

Residual Plot Starting Data for Excel

Creating a Scatterplot

Select your Data
Click Insert
Select Scatterplot
Select the first Scatterplot

Creating Scatterplot Residual Graph in Excel

The Scatterplot should look similar to the one below.

Final Scatterplot for Residual Plot for Excel

Trendline

Select + Sign in top right of the graph
Select the Arrow next to Trendline
Click More Options

Create Trendline for Residual Plot in Excel for X Y Series

4. Select Linear Trendline

5. Check Display Equation on Chart

Create Linear Trendline and Display Equation for Trendline Excel

The trendline looks similar to the one below, that also shows the equation.

Scatterplot with Trendline and Equation in Excel

Copy and Paste Equation

Highlight the equation and copy (CTRL + C)
Paste the equation on the worksheet (CTRL + V)

Equation for Residual Plot Trendline in Excel

Calculate Values

Using the equation from the previous step, create a formula: replacing x with the X Value in the table. Drag this formula down for all of the values.

Created Predicted Values for Residual Values in Excel

Table with Predicted Values

This formula calculates the predicted Y-axis values.

Final Predicted Values to Create Residual Plot in Excel

Find Residuals

Find the residuals by using the formula = Y Value – Predicted Values. Repeat this for all Values

Residual Values for Graph X Y Series Excel

Creating Residual Plot

Highlight the X Values
Hold down CTRL and highlight Residuals
Click Insert
Select Scatter
Click the first Scatterplot

Create Scatterplot for Residual Plot in Excel

Final Residual Plot

This is the final residual plot

Final Residual Graph for Plot in Excel

Calculate & Plot Residuals – Google Sheets

We’ll start with the Scatterplot below in Google Sheets

Starting Data for Scatterplot for Google Sheets

Creating a Trendline

Double Click Graph
Select Customize in Chart Editor
Click Series

Create Trendline for Residual Plot for X Y Series in Google Sheets

4. Check Trendline

5. Select Use Equation under Label

Use Trendline and Equation for Scatterplot in Google Sheets

Predicted Values

Similar to the Excel example above, plug in the X Value in the equation that was given as shown below. Do this for all of the Values. This gives us the predicted Y-Axis values.

Predicted Values for Residual Values and Equation in Google Sheets

Residual Values

Similar to Excel, you’ll repeat the same formula where =Y Value – Predicted Value

Residual Values for Graph in Google Sheets

Creating Residual Plot

Highlight Y Value and Predicted Values and Right Click
Select Hide Column C-D

Hide Values to Create Scatterplot in Google Sheets

3. Highlight X and Residual Values

4. Select Insert

5. Click Chart

Create Residual Chart in Google Sheets with X Y Series

6. Change Chart Type to Scatter Chart

Final Residual Plot for X Y Values in Google Sheets