This guide will explain how to create a residual plot in Excel to determine the goodness of fit of a particular regression model.
In statistics, the difference between an observed value and the value predicted by a model is known as the residual. Understanding the residual values in a regression model can help you assess potential biases or issues from your data.
One way to look at your model’s residual values is through a residual plot. The residual plot uses a scatterplot graph where the horizontal axis represents the predicted values of the dependent variable from the regression model, and the vertical (y) axis represents the residuals.
We can use the residual plot to identify certain properties of your model, such as non-linearity, outliers, and heteroscedasticity.
For example, if the residuals are randomly scattered around the horizontal line at y=0 (indicating zero residual error), with no discernible patterns or trends, this suggests that the regression model is a good fit for the data.
However, if there are non-random patterns in the residuals, such as a distinct funnel shape, this may indicate an issue with the model.
We can create a residual plot in Excel using the Scatterplot chart feature. Excel’s Trendline option will also help us find the linear regression equation to find the predicted values for each observation.
In this guide, we will provide a step-by-step tutorial on how to create your own residual plot in Excel. We will cover how to find the linear regression equation, how to find the residuals of each observation, and how to create a scatterplot from our data.
Let’s dive right in!
A Real Example of a Residual Plot in Excel
Let’s explore a simple example where we may need to create a residual plot in Excel.
In the table seen below, we have a dataset that we can use to create a model that predicts the median value of houses within a block based on the total number of bedrooms within that block.
We want to create a residual plot to determine whether the model fits our data well.
Using the linear regression trendline option, we’ve determined that the equation of our trendline is the following:
y=25.706x + 190015
We can substitute x with the values from the total_bedrooms field to find the predicted median house value for each observation. Afterwards, we can find the residual by subtracting the observed median house value from the prediction.
The graph above shows the residual plot of our simple linear regression model for predicting the median house value. The residual values seem to span a large range. This may indicate that the model is not the best fit for our data.
In the second example below, we have a regression model that better fits our sample dataset. The regression plot clearly shows most of the residual values lying near the horizontal axis.
This pattern indicates that most predictions are quite close to the actual observed values.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Use our sample spreadsheet to compare the residual plots of both sample datasets.
If you’re ready to try creating a residual plot yourself, head over to the next section to read our step-by-step breakdown on how to do it!
How to Create a Residual Plot in Excel
This section will guide you through each step you need to create a residual plot in Excel.
Follow these steps to start analyzing the residual plot of your regression:
- First, select the range that contains the data you will use to calculate a regression model.
- In the Insert tab, click on the Scatter icon and select the first option.
- You should now have a scatter plot chart of your data.
- Click on the scatterplot and select the ‘+’ icon to reveal the option to add a trendline. In the Format Trendline panel, ensure that we’re using a linear equation to generate the trendline.
Check the option ‘Display Equation on chart’ to reveal the equation of the trendline.
- Use the equation to find the expected or predicted value of the dependent variable.
In the example above, we’ve created a new column in our dataset that uses the linear equation of our regression model to predict the median house value.
- We can find the residual value by finding the difference of the expected value and the observed value.
- Select the columns that contain the predicted dependent variable and the residuals.
- In the Insert tab, click on the Scatter icon and select the first option. You should now have a residual plot of your regression model.
Frequently Asked Questions (FAQ)
Here are some frequently asked questions about this topic:
- What is the ideal residual plot?
The ideal residual plot, also known as a null residual plot, should be randomly scattered along the horizontal axis. The spread of the residuals should remain constant throughout the range of the predictor variable.
A null residual plot is a good indication that the linear regression model is a good fit for the data. - What are the possible reasons why a residual plot shows a pattern?
Residuals may follow a pattern or trend in a residual plot for several reasons. Possible issues include omitted variables, nonlinear relationships, and the presence of outliers or data entry errors. Non-random patterns in your residuals signify that your regression model may require re-evaluation.
This tutorial should cover everything you need to know about setting up a residual plot in Excel.
We’ve explained how to find the residuals for each observation in a dataset and plot them on a scatterplot chart.
Creating a residual plot is just one statistical method you can perform in Excel. For example, Excel offers several methods to perform linear regression. You can read our step-by-step guide on that topic to learn more.
You may also check our guide on how to add a regression line to a scatterplot chart in Microsoft Excel to know more about how we found the equation of our regression model.
That’s all for this guide on creating a residual plot in Excel! If you’re still looking to learn more about Microsoft Excel, be sure to check out our library of Excel resources, tips, and tricks!
