Calculate & Plot Residuals – Excel & Google Sheets

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

  1. Select your Data
  2. Click Insert
  3. Select Scatterplot
  4. 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

  1. Select + Sign in top right of the graph
  2. Select the Arrow next to Trendline
  3. 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

  1. Highlight the equation and copy (CTRL + C)
  2. 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

  1. Highlight the X Values
  2. Hold down CTRL and highlight Residuals
  3. Click Insert
  4. Select Scatter
  5. 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

  1. Double Click Graph
  2. Select Customize in Chart Editor
  3. 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

  1. Highlight Y Value and Predicted Values and Right Click
  2. 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