# 2 Ways to Make Residual Plot in Excel

To find the non-linear values in a specified range, you may need to apply the **Residual Plot** to your assignments. In this article, I will explain how you can make the **Residual Plot** using Microsoft Excel. You will get a complete idea of how to use Excel to make the residual plot after going through this article. Let’s begin!

### What is a Residual Plot?

The **Residual Plot** is the display of the distance between a selected set of data points and their regression line shown on a scatter plot. In simple language, a **Residual Plot** is the difference between the actual values and the expected values.

### Introduction to the Dataset

First, get familiar with the dataset in this article. I have built a data table that shows how many cars are sold based on the number of ads that have been published over the past 10 weeks.

The number of cars sold is dependent on the number of advertisements that are published. So, according to the equation of the straight line, the ads are independent variables (**X**) and the cars are dependent variables (**Y**).

## Easiest Way to Make a Residual Plot in Excel

To use this method, you first have to determine the residual values. After that, creating the residual plot is simple.

**Usage Guide**

**Step_1**: Select the cell range **B1:C11**.

These are the **X** and** Y** values.

**Step_2**: Go to the **Insert** tab.

**Step_3**: Select a** Scatter Plot** from the **Charts** group.

I picked up the first **Scatter Plot **from the **Scatter ****drop-down menu**.

**Step_4**: Now go to **Chart Elements>Trendline>More Options**.

The **Format Trendline** task pane will appear on the right side of the worksheet.

**Step_5**: Select the option **Display Equation on Chart**.

Instantly, the equation will appear beside the trendline of the chart.

**Step_6**: Next, create a new column named **Expected Values**.

The location of the new column in my data table is **column D**.

**Step_7**: Copy the equation from the chart except for the letter **Y**.

**=1.2327x + 26.6**

**Step_8**: Paste the formula in cell **D2**, delete the **X**, and then insert **B2** in the formula.

**=1.2327B2 + 26.6**

**Formula Explanation**

The equation of a linear trendline is: **y= mx + c**

**y**represents the**Y**-coordinates**1.2327**is the value of**m**(slope)**B2**is the value of**X**-coordinates**26.6**is the value of**c**(vertical intercept)

The values of the **X** variable are added to the formula this way.

**Step_9**: Then take the cursor in the right bottom corner and drag the **Fill Handle** icon from cell **D2:D11**.

**Step_10**: Add another column named **Residuals** in the location **column E**.

**Step_11**: Type this formula in cell **E2**:

**=C2-D2**

**Formula Explanation**

**C2**is the actual value of the**Y**variable.**D2**is the expected value of the**X**variable.

**Step_12**: Press the **ENTER** key.

**Step_13**: Drag the **Fill Handle** icon in the cell range **E2:E11**.

**Step_14**: Now select cell range **B1:B11**, press **CTRL**, and select cell **E1:E11**.

**Step_15**: Go to the **Insert tab > Charts group > Scatter Plot**.

**Final Result >**

A residual plot like the one I have below will appear on your Excel sheet:

After a few modifications, I gave the residual plot the following final appearance:

## Alternative Way: Make a Residual Plot in Excel with Data Analysis ToolPak

Before showing you this technique, a gentle reminder is that if your Excel doesn’t already have the **Data Analysis** command, **click here** and follow the steps.

**Usage Guide**

**Step_1**: Open the worksheet that has the data table.

**Step_2**: Go to the **Data** tab.

**Step_3**: Click on the **Data Analysis** command from the **Analysis** group.

The **Data Analysis** dialog box will pop up on the worksheet.

**Step_4**: Select **Regression** from the **Analysis Tools** list.

**Step_5**: Hit the **OK** button.

After that, the **Regression** window will appear on the Excel sheet.

**Step_6**: Inside the **Input Y Range** box, insert the cell range **C2:C11**.

**Step_7**: Inside the **Input X Range** box, insert the cell range **B2:B11**.

**Tip**: If you have the data labels, select **Labels**.

**Step_8**: Select an empty cell, cell **A14** in the **Output Range**.

**Step_9**: Select **Residuals**.

**Step_10**: Select **Residual Plots**.

**Step_11**: Hit the **OK** button.

**Final Result >**

The **Summary Output** will take place in cell **A14**, and the** Residual Plot** will be beside the table.

## Conclusion

You can apply any of the methods in this article. Both will work perfectly and the second method will give you additional details like residual statistics, ANOVA table, etc. Share your comments about this blog in the comment section. Also, keep visiting our site to learn more about Microsoft Excel. Happy Excelling!