Find Outliers (IQR Method)

All versionsQUARTILEIQR

Outliers — values far outside the normal range — can wreck an average or hide a data-entry error. The standard, robust way to flag them is the IQR (interquartile range) rule built on QUARTILE.

Quick formula: a value is an outlier if it’s outside Q1−1.5×IQR or Q3+1.5×IQR:

=OR(B2 < Q1 - 1.5*IQR, B2 > Q3 + 1.5*IQR)

IQR = Q3 − Q1 (the middle 50% spread); anything more than 1.5×IQR beyond the quartiles is flagged.

Functions used (tap for the full reference guide):

QUARTILE OR IF

The example

Most values cluster near 50; the 200 is an outlier.

	A	B
1	Value	Outlier?
2	48	No
3	52	No
4	50	No
5	200	Outlier
6	49	No

The formula

With Q1/Q3 computed from the data, the flag in B2:

=IF(OR(A2 < $E$1 - 1.5*$E$3, A2 > $E$2 + 1.5*$E$3), "Outlier", "") // E1=Q1, E2=Q3, E3=IQR

How it works

The IQR rule defines “normal” bounds:

Compute the quartiles: Q1 = QUARTILE(range, 1) and Q3 = QUARTILE(range, 3). The IQR is Q3 − Q1 — the spread of the middle 50%.
The “fences” are Q1 − 1.5×IQR (lower) and Q3 + 1.5×IQR (upper).
Any value outside those fences is flagged as an outlier — the 200 here.
The 1.5 multiplier is the convention (3× flags only extreme outliers). It’s robust because quartiles aren’t swayed by the outliers themselves.

Alternative: z-scores. Flag values more than 3 standard deviations from the mean: =ABS(A2 - AVERAGE($A$2:$A$100)) > 3*STDEV.S($A$2:$A$100). The IQR method is sturdier when outliers are present, since they don’t distort the quartiles.

Try it: interactive demo

Live demo

Type values (commas); outliers by the IQR rule are flagged.

Values

Variations

Z-score method

More than 3 SD from the mean:

=ABS(A2 - AVERAGE($A$2:$A$100)) > 3*STDEV.S($A$2:$A$100)

Extreme outliers only

Use a 3× multiplier instead of 1.5×:

=OR(A2 < Q1 - 3*IQR, A2 > Q3 + 3*IQR)

Highlight outliers automatically

Drop the same OR test into a conditional-formatting rule.

Pitfalls & errors

Use .INC quartiles consistently. Mixing QUARTILE.INC and .EXC shifts the fences. The classic QUARTILE = .INC.

Small samples are noisy. With very few data points, quartiles (and the fences) are unstable — the rule works best on a decent number of values.

An outlier isn’t always an error. It may be a real, important extreme. Investigate before deleting — flagging ≠ removing.

Practice workbook

📊

Download the free Find Outliers (IQR Method) practice workbook

A value column with live Q1/Q3/IQR fences and an outlier flag, the z-score and extreme-outlier variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find outliers in Excel?

Use the IQR rule: compute Q1=QUARTILE(range,1), Q3=QUARTILE(range,3), and IQR=Q3-Q1. Flag values below Q1-1.5*IQR or above Q3+1.5*IQR as outliers.

What's the IQR method for outliers?

The interquartile range (Q3-Q1) measures the middle 50% spread. Values more than 1.5 times the IQR beyond Q1 or Q3 are considered outliers. It's robust because quartiles aren't distorted by the outliers.

Should I use the IQR or z-score method?

The IQR method is sturdier when outliers are present because they don't skew the quartiles. The z-score method (more than 3 SD from the mean) works well for roughly normal data without extreme values.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: QUARTILE · OR · IF