A residual plot is a type of plot that displays the fitted values against the residual values for a regression model.
This type of plot is often used to assess whether or not a linear regression model is appropriate for a given dataset and to check for heteroscedasticity of residuals.
This tutorial explains how to create a residual plot for a simple linear regression model in Excel.
How to Create a Residual Plot in Excel
Use the following steps to create a residual plot in Excel:
Step 1: Enter the data values in the first two columns. For example, enter the values for the predictor variable in A2:A13 and the values for the response variable in B2:B13.
Step 2: Create a scatterplot. Highlight the values in cells A2:B13. Then, navigate to the INSERT tab along the top ribbon. Click on the first option for Scatter within the Charts area.
The following chart will appear:
Step 3: Display trend line equation on the scatterplot. Click “Add Chart Elements” from the DESIGN tab, then “Trendline”, and then “More Trendline Option. Leave “Linear” selected and check “Display Equation on Chart.” Close the “Format Trendline” panel.
The trend line equation will now be displayed on the scatterplot:
Step 4: Calculate the predicted values. Enter the trendline equation in cell C2, replacing “x” with “A1” like so:
Then, click cell C2 and double-click the small “Fill Handle” at the bottom right of the cell. This will copy the formula in cell C2 to the rest of the cells in the column:
Step 5: Calculate the residuals. Enter B2-C2 in cell D2. Then, click cell D2 and double-click the small “Fill Handle” at the bottom right of the cell. This will copy the formula in cell D2 to the rest of the cells in the column:
Step 6: Create the residual plot. Highlight cells A2:A13. Hold the “Ctrl” key and highlight cells D2:D13. Then, navigate to the INSERT tab along the top ribbon. Click on the first option for Scatter within the Charts area. The following chart will appear:
This is the residual plot. The x-axis displays the fitted values and the y-axis displays the residuals.
Feel free to modify the title, axes, and gridlines to make the plot look more visually appealing:
This was very helpful. It would be even more helpful if it offered some guidance on how to evaluate the residuals other than visually and, more importantly, how to correct for any bias.
Thanks for this.
This is of great use, thank you for your help!
I just want to say, you did such a great job explaining this to someone who has no idea what they are doing. I feel really confident in the work I am going to submit. You are awesome. Keep it up for us less fortunate. 🙂
THANKYOU SO MUCH I LITERALLY WAS GOING TO FAIL MY ASSESMENT
Thanks for this Zach, it really helped. One error I’m noticing in the blog post, for the residual plot, you used Predictor vs Residual, but it should be Predicted vs Residual.
Super helpful, thank you so much. Easy and clear instructions.
This was really helpful. Thank you.
Kindly add the interpretation next time or update it.
Hi!
Could you help me understand how should I calculate residues when using a ponderated data?
Hi Daniel…When working with weighted (ponderated) data, calculating residuals requires taking the weights into account to ensure that the residuals properly reflect the importance of each data point. Here’s how you can do it:
### Steps to Calculate Weighted Residuals in Excel:
1. **Calculate the Predicted Values**:
– Use your model (e.g., a regression equation) to calculate the predicted values for each observation.
2. **Calculate the Residuals**:
– Subtract the predicted value from the actual observed value for each data point.
– Residual \( r_i \) = Observed value \( y_i \) – Predicted value \( \hat{y}_i \).
3. **Apply the Weights**:
– Multiply the residuals by the square root of the corresponding weights to obtain the weighted residuals.
– Weighted Residual \( wr_i \) = Residual \( r_i \) * \( \sqrt{w_i} \), where \( w_i \) is the weight for observation \( i \).
4. **Create the Residual Plot**:
– Plot the weighted residuals against the predicted values or another relevant variable.
– In Excel, you can do this by selecting the appropriate columns for the x-axis (e.g., predicted values) and the y-axis (weighted residuals) and inserting a scatter plot.
### Example:
Let’s say you have the following data:
– Observed Values (Y): 10, 15, 20
– Predicted Values (Ŷ): 12, 14, 19
– Weights (W): 0.5, 1.5, 2
Steps:
1. **Calculate Residuals**:
– Residual 1: \( 10 – 12 = -2 \)
– Residual 2: \( 15 – 14 = 1 \)
– Residual 3: \( 20 – 19 = 1 \)
2. **Apply the Weights**:
– Weighted Residual 1: \( -2 \times \sqrt{0.5} = -1.41 \)
– Weighted Residual 2: \( 1 \times \sqrt{1.5} = 1.22 \)
– Weighted Residual 3: \( 1 \times \sqrt{2} = 1.41 \)
3. **Plot**:
– Use Excel’s scatter plot feature to plot these weighted residuals against the predicted values.
This process gives you a residual plot that reflects the relative importance of each data point according to the weights assigned.