How to Find and Plot Residuals in Excel
Residual plots play an essential role in regression analysis. They tell you how accurately your regression line represents the relationship between two variables. The following guide explains how to calculate and plot Excel residuals using two methods.
But before getting to the good stuff, let’s briefly understand residuals and their importance in a regression analysis.
What Are Residuals, And Why Are They Important?
A residual is a difference between the observed (actual) value and the predicted value. In simpler terms, a residual is an error. For example, if the observed value is 10, and your model gave a value of 8, then the residual value would be 2.
On the other hand, a residual plot is a scatter plot in which the independent variable (age) is plotted on the horizontal axis, and the residuals (errors) are plotted on the vertical axis.
Ok, you know what residuals are, but why are they important? To understand that, consider the following case:
We want to understand the relationship between age and savings rates. For that, we’ll plot a regression line. However, we’re unsure if the regression line accurately represents the relationship between the two variables. That’s where residuals come in. We use a residual plot to check the accuracy of the regression line.
Now that you know what residuals are, let’s learn how to create a residual plot in Excel:
How to Create a Residual Plot Using Trendline Equation
The first method of plotting residuals in Excel uses a trendline equation to calculate the predicted values for our data. Once we have that, we can calculate the residuals by subtracting the predicted values from the observed values. Finally, we create a scatter plot with the predictors on the horizontal axis and the residuals on the vertical axis.
While this method requires more steps than the second method, it’s a great way to understand the concept. Once you’ve got a decent understanding, you can use the second method, which requires less time and effort.
Let’s walk you through the process, one step at a time:
Step 1: Enter the Data
We start by entering the values for the predictor (under theXcolumn) and the observed values (under theYcolumn):
Step 2: Create a Scatter Plot of the Data
Now we create a scatter plot of the data by following the steps below:
If you’re unfamiliar with scatter plots, here’s a comprehensive article oncreating a scatter plot in Excel and presenting your data.You can also learn touse a scatter plot in Excel to predict data behavior.
Step 3: Add a Trendline and Display the Trendline Equation on Scatterplot
Follow the steps below to add a trendline to the scatterplot:
You’ll now see a line (trendline) intersecting the dots on your scatter plot. To display the trendline equation on the scatterplot, follow the steps below:
Step 4: Calculate the Predicted Values
To calculate the predicted values, create another column (Z) next to your data and follow the steps below:
Step 5: Find the Residual Values
Now that we’ve got the predicted values, we can find the residual values by subtracting the predicted values from the observed (actual) values under the Y column. The formula for the first residual value would be=B2-C2. For the second, the formula would be=B3-C3, and so on.
Step 6: Create the Residual Plot
To create a residual plot, we need the predictor and residual values. Now that we have both, follow the steps below:
How to Create a Residual Plot With the Analysis ToolPak in Excel
For the second method, all you have to do is provide Excel with the data, and it does all the work. You can create a residual plot in only a few clicks. But for that, you’ll need to load Excel’s Analysis Toolpak. Let’s get started:
Step 1: Load the Analysis Toolpak
Step 2: Enter the Data
You’ll notice that the graph has changed after unhiding the columns. To change it back to the original residual plot, we’ll need to hide theYandZcolumns (by selecting theYandZcolumns, right-clicking the mouse, and selectingHide).
Step 3: Create the Residual Plot
In theRegressiondialog box, under theOutput Options, checkNew Worksheet Ply, and clickOK.
Excel will automatically create the residual plot along with the following outputs that it’s possible to use to check the reliability of your regression model:
Explore Excel’s Analysis ToolPak
Excel’s Analysis ToolPak offers a range of valuable statistics functionality that you could use to analyze your datasets. After creating the residual plot through Excel’s Analysis ToolPak, you might start scratching your head while looking at all the complicated tables. But once you learn how to do fundamental data analysis in Excel, the numbers don’t look so scary.
Excel isn’t meant for data analysis, but it can still handle statistics. We’ll show you how to use the Data Analysis Toolpak add-in to run Excel statistics.
Windows is great, but adding this makes it unstoppable.
Goodbye sending links via other apps.
You’re not getting the most out of what you pay for iCloud+.
You’ve been quoting these famous films wrong all along!
These are the best free movies I found on Tubi, but there are heaps more for you to search through.
