How to Find Outliers in Excel
One fat-fingered number can wreck an average or a chart. Here are the two standard ways to find outliers — the z-score and the IQR method — plus a one-click way to flag them.
Last updated: August 2026
| Value | Z-score | Outlier? | |
|---|---|---|---|
| 102 | 0.2 | → | no |
| 98 | -0.3 | → | no |
| 1500 | 3.9 | → | outlier |
Method 1 — the z-score
- Put your numbers in a column, say A2:A100.
- In a helper column:
=(A2-AVERAGE(A$2:A$100))/STDEV.P(A$2:A$100). - Flag outliers with
=IF(ABS(B2)>3, "outlier", "")— a z-score past ±3 is roughly 3 standard deviations from the mean.
Best when the data is roughly bell-shaped. It can be dragged around by the very extremes you're hunting.
Method 2 — the IQR (quartile) method
Q1 =QUARTILE.INC(A$2:A$100, 1)andQ3 =QUARTILE.INC(A$2:A$100, 3).IQR = Q3 - Q1.- Flag anything
< Q1 - 1.5*IQRor> Q3 + 1.5*IQR.
More robust for skewed data, since quartiles aren't thrown off by a single huge value.
The 1-click way — Tellsheet
Flag Outliers applies either method for you: pick your numeric column, choose z-score or IQR (and a threshold if you want), and it adds a column marking each outlier — easy to filter, sort, or remove. Pairs with Column Health Scan and Group & Summarize. Runs entirely on your computer.
Frequently asked questions
What is the formula for finding outliers in Excel?
Z-score: =(value - AVERAGE(range)) / STDEV.P(range), outlier if the absolute value exceeds 3. IQR: flag values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR. Tellsheet's Flag Outliers applies either in one click.
Should I use z-score or IQR?
Z-score assumes a roughly normal distribution; IQR is based on quartiles and is more robust for skewed data. When unsure, try IQR first. Flag Outliers offers both.
How do I highlight outliers automatically?
Use Conditional Formatting with a formula that returns TRUE for outliers, or Tellsheet's Flag Outliers, which adds a filterable column instead of just colouring cells.
How do I remove outliers once I've found them?
Flag them first, then filter the flag column and delete those rows — or exclude them from your average with AVERAGEIF. Flagging (not auto-deleting) keeps you in control of what's really an error vs a real extreme.
Related Excel guides
Flag outliers in one click
Flag Outliers marks the values skewing your data with the z-score or IQR method — right inside Excel, nothing uploaded.
Get Tellsheet free See pricing