How Do You Find Outliers in Regression Analysis in Excel?

Run a regression in Excel and you get a tidy set of numbers: an R Square, a slope, an intercept, a p-value. What you do not get is a warning that one row of your data is quietly doing most of the work.

That happens more often than people expect. A single mistyped figure, one unusual customer, one month where a promotion distorted the pattern, and the line Excel fits stops describing your data and starts describing that one point. Your R Square can look healthy while the model is misleading you.

Finding those points is a specific, repeatable job, and Excel can do all of it. This guide covers the four measures that matter, the two routes to calculate them, and the part most tutorials skip: deciding what to actually do once you have found one.

The Short Answer

To find outliers in an Excel regression, run Data > Data Analysis > Regression, tick both Residuals and Standardized Residuals, and read the RESIDUAL OUTPUT table that Excel adds beneath the coefficients.

Any row with a standardized residual whose absolute value is greater than 3 is conventionally treated as an outlier. Some analysts use a stricter cutoff of 2, which flags more rows and catches more borderline cases.

That gets you the obvious ones. But a standardized residual only measures how far a point sits from the fitted line vertically. It does not tell you whether that point is *bending* the line. For that you need leverage and Cook's distance, which Excel will not calculate for you but which you can build in a few columns. The rest of this guide covers both.

What Counts as an Outlier in a Regression?

"Outlier" gets used loosely. In regression there are three distinct things going on, and conflating them is how people end up deleting the wrong rows.

Residuals: Distance From the Line

A residual is simply the actual value minus the value your model predicted. A large residual means the model got that observation badly wrong.

Raw residuals are hard to judge because their size depends on the units of your data. A residual of 400 is enormous if you are predicting shoe sizes and trivial if you are predicting annual revenue.

Standardized Residuals: Distance in Comparable Units

Standardizing fixes the units problem by expressing each residual in standard-deviation terms. A standardized residual of 2.5 means that observation sits two and a half standard deviations away from where the model put it, regardless of what you are measuring.

This is the measure Excel gives you directly, and for most everyday analysis it is enough.

Leverage: Unusual on the Input Side

Leverage measures how far an observation's *predictor* value sits from the average predictor value. A point with high leverage is unusual in x, not in y.

High leverage matters because those points have disproportionate power over the fitted line. A point at the far end of your x range acts like a long lever: move it slightly and the whole line pivots.

Influence: Points That Actually Change the Model

Influence combines the two. A point is influential when it has both a sizeable residual and high leverage, and removing it would visibly change your coefficients.

This is the category that actually matters. A point can have a big residual and barely affect the model. A point can have high leverage and sit perfectly on the line, changing nothing. The dangerous case is both at once, and Cook's distance is the measure built to catch it.

Working with data you are not sure you can trust? A free SEO audit from SEO Stuff shows you which numbers in your own reporting are load-bearing and which are noise. Get your free audit.

How to Find Outliers Using the Analysis ToolPak

This is the fastest route and it covers residuals and standardized residuals in one pass.

1. Confirm the Analysis ToolPak is loaded. Look for a Data Analysis button at the right-hand end of the Data tab. If it is not there, enable it through File > Options > Add-ins, choose Excel Add-ins in the Manage box, click Go, and tick Analysis ToolPak.
2. Lay your data out in two clean columns with no blank rows: your dependent variable (y) and your independent variable (x).
3. Click Data > Data Analysis, select Regression from the list, and click OK.
4. Set Input Y Range to your dependent column and Input X Range to your independent column. Include the header cells and tick Labels so the output is readable.
5. Tick Residuals and Standardized Residuals. These are the two that matter here.
6. Tick Residual Plots as well. A scatter of residuals against the predictor is the single fastest way to spot a point that does not belong.
7. Choose an output location. A new worksheet keeps things tidy, since the output needs roughly seven columns of space.
8. Click OK.

Excel produces three summary tables followed by the residual detail. The Regression Statistics table gives you Multiple R, R Square, Adjusted R Square, Standard Error and the observation count. Below that sits an ANOVA table, then the Coefficients table with your slope and intercept. Underneath all of it is RESIDUAL OUTPUT, one row per observation, with the predicted value, the residual and the standardized residual.

Reading the Residual Output

Sort or conditionally format the standardized residual column by absolute value and look at the top of the list. In a clean dataset almost everything falls between −2 and 2. Anything past 3 is worth investigating; anything past 4 is almost certainly either a data-entry error or a genuinely different kind of observation.

A quick way to flag them without sorting: put `=IF(ABS(C2)>3,"CHECK","")` beside the standardized residual column and fill it down.

One Thing to Know About Excel's Standardized Residuals

Excel's Standardized Residuals column divides each residual by a single standard-deviation figure calculated across the whole residual set. It does not adjust for how much leverage each individual observation has.

Statistical software typically reports a *studentized* residual instead, which does make that adjustment. The formula is the residual divided by the residual standard error multiplied by the square root of one minus that observation's leverage.

In practice the two agree closely for points in the middle of your x range and diverge at the edges, which is exactly where the interesting points tend to sit. So treat Excel's column as a good first filter rather than a final verdict.

How to Calculate Leverage and Cook's Distance

Excel has no built-in function for either, but both are short formulas.

Leverage

For a simple regression with one predictor, an observation's leverage is one divided by the number of observations, plus the squared deviation of that observation's x value from the mean x, divided by the total sum of squared deviations in x.

In a worksheet where your x values sit in column A, rows 2 to 51:

• In a helper cell, calculate the sum of squared deviations: `=DEVSQ($A$2:$A$51)`
• In a leverage column: `=1/50 + (A2-AVERAGE($A$2:$A$51))^2/DEVSQ($A$2:$A$51)`

Leverage values always sum to the number of coefficients in the model, so the average leverage is small. A common rule of thumb flags any observation whose leverage exceeds two or three times that average.

Not sure whether your traffic data is telling you something real? Book a free SEO audit and get a plain-English read on which movements in your numbers are signal.

Cook's Distance

Cook's distance asks a direct question: how much would the fitted model change if this one observation were deleted?

With the residual in column D, leverage in column E, the number of predictors as k, and the mean squared error from the ANOVA table:

`=(D2^2/((k+1)*MSE))*(E2/(1-E2)^2)`

For a single-predictor regression, k is 1, so the first denominator is 2 times MSE. The MSE is the "MS" figure on the Residual row of Excel's ANOVA table.

Two thresholds are in common use. The conservative one flags any observation with a Cook's distance above 1. The more sensitive one flags anything above 4 divided by the number of observations, which for 50 rows means 0.08.

Sort descending and look at the gap rather than the absolute number. If one observation has a Cook's distance five times larger than every other, that is your influential point whichever threshold you prefer.

How to Find Outliers Without the ToolPak

If the ToolPak is unavailable, which happens on locked-down work machines and on Excel for the web, you can do all of this with worksheet functions.

1. Calculate the slope with `=SLOPE(y_range, x_range)` and the intercept with `=INTERCEPT(y_range, x_range)`.
2. Build a predicted column: `=$slope*A2+$intercept`. Alternatively use `=TREND(y_range, x_range, A2)` in one step.
3. Build a residual column: actual minus predicted.
4. Standardize the residuals by dividing each one by `=STDEV.S(residual_range)`.
5. Add leverage and Cook's distance as above.

`=LINEST(y_range, x_range, TRUE, TRUE)` entered as an array returns the slope, intercept, their standard errors, R Square, the standard error of the estimate, the F statistic, degrees of freedom, and the regression and residual sums of squares. That gives you everything the ToolPak summary tables provide, without the add-in.

What to Do When You Find One

This is where judgement replaces arithmetic, and the honest answer is that deleting the point is usually the wrong first move.

Work through it in order:

1. Check whether it is a data error. A decimal in the wrong place, a value in the wrong row, a total accidentally included with the detail rows. If you can trace it to a mistake, fix it or remove it and document why.
2. Check whether it belongs to your population. A wholesale order sitting in a dataset of retail transactions is not an outlier, it is a different thing measured by accident. Remove it and say so.
3. Check whether your model is wrong. A run of large residuals at one end of the x range usually means the relationship is curved, not that those points are bad. A log transform or a quadratic term often makes them disappear.
4. If it is real and it belongs, keep it. Report the regression both with and without it. If the coefficients barely move, the point does not matter and you can stop worrying. If they move a lot, that instability is itself the finding, and hiding it by deleting the row is the one genuinely indefensible option.

Never delete a point simply because it worsens your R Square. That is fitting the data to the conclusion.

Want a second pair of eyes on your analysis? The team at SEO Stuff reviews reporting setups every day. Request a free audit and find out what your data is actually saying.

Limitations of Doing This in Excel

Excel handles the standard case well, but there are real edges.

• No studentized residuals, no Cook's distance, no DFFITS out of the box. You can build them, but every extra column is another formula to get wrong.
• Multiple regression makes leverage much harder. The simple leverage formula above only works with one predictor. With several, leverage comes from the hat matrix, which needs matrix functions such as `MMULT` and `MINVERSE` and becomes genuinely awkward.
• The ToolPak output is static. Change your data and the regression does not recalculate. You have to run it again, and it is easy to end up reading a stale table.
• Row limits and speed. Fine at thousands of rows, painful at hundreds of thousands.
• No automatic diagnostics. Statistical packages flag influential observations for you. Excel waits for you to ask.

Excel Versus the Alternatives

If regression diagnostics are a regular part of your work rather than an occasional check, the comparison is worth making honestly.

Excel wins on accessibility. It is already installed, everyone can open your file, and the residual output is legible to people who have never taken a statistics course. For a one-off check on a few hundred rows, nothing is faster.

R and Python win on completeness. A single call in either returns residuals, studentized residuals, leverage, Cook's distance and DFFITS together, along with diagnostic plots. If you are running this weekly, the setup cost pays back quickly.

Dedicated statistics software wins on defensibility. If your analysis will be scrutinised, tools built for the job produce output that reviewers recognise.

Power BI and Tableau win on presentation, not diagnostics. Both will draw you a trend line. Neither is built for deciding whether a point should be in the model.

The practical answer for most people is Excel for the first look and something else when the first look turns up something complicated.

Final Thoughts

Outlier detection in Excel regression comes down to a sequence. Run the regression with Residuals and Standardized Residuals ticked. Scan the standardized residual column for anything past 3. Add leverage and Cook's distance when you need to know whether a point is genuinely bending the line rather than just sitting away from it. Then investigate the point rather than deleting it.

The habit worth building is looking at the residual plot every single time, before you look at R Square. A model can have a perfectly respectable R Square and an obviously broken residual pattern, and the residual plot shows you that in a second.

If the Data Analysis button is missing from your ribbon, our guide on how to get the Data Analysis ToolPak in Excel walks through enabling it on Windows and Mac.

Ready to find out what your own data is hiding? Get a free SEO audit from SEO Stuff and see which of your numbers deserve your attention.