How to Easily Create a Residual Plot in Google Sheets

Understanding the Role of the Residual Plot

A residual plot is a type of scatter plot that displays the fitted values (or the predictor variable values) on the x-axis against the corresponding residual values on the y-axis for a regression model. This configuration is specifically designed to highlight patterns in the error term.

This type of plot is often used to assess whether or not a simple linear regression model is appropriate for a given dataset. If the relationship between variables is truly linear and the model assumptions hold, the residuals should exhibit random scatter around the zero line. Deviations from this pattern—such as a curved relationship or a fan-like spread—signal that the model may be misspecified or that key assumptions have been violated.

The following step-by-step example explains exactly how to calculate the necessary components and create a diagnostic residual plot for a simple linear regression model in Google Sheets.

Step 1: Enter the Data

The foundational step for any regression analysis is organizing your predictor (independent) and response (dependent) variables. For this example, we will work with a small dataset consisting of twelve paired observations. Ensure that the independent variable (X) is placed in column A and the dependent variable (Y) is placed in column B.

First, let’s enter the following values for a dataset, labeling the columns clearly for readability and ease of reference in later steps:

It is crucial to verify the accuracy of the data entry, as any transcription errors at this stage will cascade through all subsequent calculations, leading to an inaccurate regression line and consequently, a misleading residual plot. We will use these original observation pairs to derive both the predicted values and the error terms (residuals).

Step 2: Calculate the Equation of the Regression Model

To calculate the predicted values for the linear regression, we first need to determine the slope (m) and the y-intercept (b) of the line of best fit. The linear equation is represented as Y = mx + b. Fortunately, Google Sheets offers built-in statistical functions that automate these complex calculations.

We will use the SLOPE function to find the angular coefficient (m) and the INTERCEPT function to find the constant term (b). The syntax for both requires specifying the ‘data_y’ range (column B) first, followed by the ‘data_x’ range (column A). Input these formulas into separate cells, perhaps B15 and B16, as shown below:

Using these calculated values, where the slope is approximately 0.755 and the intercept is 29.631, we can formalize the simple linear regression equation for this specific dataset:

ŷ = 29.631 + 0.755x

This equation represents the line that minimizes the sum of squared errors between the observed data points and the line itself. These parameters are fundamental to calculating the predicted values in the next step.

Step 3: Calculate the Predicted Values

Once we have established the regression equation, we can calculate the predicted dependent variable (Ŷ) for every observation in our dataset. The predicted value is what our model estimates Y to be, given the input X value. This calculation involves substituting each X value (from column A) into the derived equation: Ŷ = Intercept + (Slope * X).

To implement this efficiently in Google Sheets, we need to ensure that the references to the Slope ($B$15) and Intercept ($B$16) remain fixed when copying the formula down the column. This is achieved using absolute references (the dollar sign prefix). We’ll type the following formula into cell C2, which represents the predicted Y value for the first observation:

=$B$16+$B$15*A2

We can then copy and paste this formula down to every remaining cell in column C (down to C13). Column C now contains the predicted values generated by our linear regression model, which are necessary for calculating the core component of our plot: the residuals.

Step 4: Calculate the Residuals

The residual is the cornerstone of the residual plot. A residual represents the vertical distance between an actual observed data point and the corresponding point on the regression line. It quantifies the error of the model’s prediction for that specific observation.

The calculation is straightforward but critical:

Residual = Observed value (Y) – Predicted value (Ŷ)

To calculate the residual for each observation in our dataset, we will create a new column (D) and type the following simple subtraction formula into cell D2, referencing the observed Y value in B2 and the predicted Y value in C2:

=B2-C2

We can then copy and paste this formula down to every remaining cell in column D (down to D13). These values—some positive (under-predicted), some negative (over-predicted)—will form the vertical axis of our diagnostic plot.

Step 5: Create the Residual Plot

With the residuals calculated, we are ready to construct the plot. The residual plot requires two variables: the independent variable (X) from column A, and the residuals (error term) from column D.

To create the residual plot in Google Sheets, we must select these two non-contiguous columns. Highlight the values in the range A2:A13 (X values), then hold the Ctrl key (or Command on Mac) and highlight the values in the range D2:D13 (Residuals). This selection pairs the input variable with its corresponding error:

Next, click the Insert tab, then click Chart in the dropdown menu. Google Sheets often defaults to a line or bar chart, which is incorrect for this purpose.

In the Chart editor panel that appears on the right side of the screen, navigate to the Setup tab and choose Scatter chart as the Chart type. This will display the individual data points necessary for pattern recognition:

Upon selecting the scatter chart type, the following residual plot will automatically appear, visually mapping the errors of your model:

Interpreting the Results of the Residual Plot

The resulting plot displays the values for the predictor variable (X) on the x-axis and the residuals (Y) on the y-axis. The zero line on the y-axis represents a perfect prediction (where the observed value equals the predicted value). The interpretation of this plot is critical for determining the validity of the simple linear regression model.

One key assumption of linear regression is that the residuals have constant variance at every level of X (known as homoscedasticity). We use a residual plot primarily to determine if this assumption is met. If the residuals are roughly evenly scattered around zero in the plot, exhibiting no discernible pattern or widening/narrowing trend, then the assumption of constant variance is generally considered met.

In our residual plot above, we can observe that the data points appear to be randomly scattered both above and below the zero line, with no obvious curvature (suggesting linearity is met) and without a funnel or cone shape (suggesting constant variance is met). Thus, based on this visual diagnostic, we would conclude that the assumption of constant variance is met for this particular regression model, reinforcing confidence in the model’s fitted line.

Summary and Best Practices

Creating a residual plot is an indispensable step beyond simply generating a regression line. While the line itself provides the best fit for the data, the residual plot offers powerful diagnostic insights into the quality and statistical viability of that fit. If a plot reveals a curved pattern, it suggests a non-linear relationship exists that the linear model fails to capture. If the spread of residuals increases or decreases as X increases (heteroscedasticity), then the standard errors and confidence intervals derived from the model will be unreliable.

Always pair the quantitative results of your regression (coefficients, R-squared) with the qualitative diagnostic assessment provided by the residual plot. The visual evidence of random scatter around zero is the strongest indicator that the core assumptions required for reliable inference using ordinary least squares (OLS) regression have been satisfied. By following this meticulous process in Google Sheets, you ensure a higher standard of statistical rigor in your analysis.