How to find outliers with the IQR method in Excel
Formula (Excel and Google Sheets)
=IF(OR(B2<$G$2,B2>$H$2),"Outlier","")Flag a value as an outlier when it is below Q1 − 1.5×IQR or above Q3 + 1.5×IQR, where IQR = Q3 − Q1. Put Q1 and Q3 in cells, then test each row against the two fences.
- Result on the example
- one result per row in
C2, filled down toC11 - Works in
- Excel 2010 and later
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C11. Change any input and the result updates, or edit the formula to experiment.
How it works
The interquartile range (IQR) is the spread of the middle half of the data. Anything more than 1.5 IQRs beyond the quartiles is unusual compared with the rest, which is the rule behind box plots.
Keep the fences in their own cells and lock them with $, so one formula can be filled down and the flags move when the data changes.
=IF(OR(B2<$G$2,B2>$H$2),"Outlier","")Result""
B234The value in B2.
$G$224.875The value in G2.
B2<$G$2FALSEThe < operator tests less than.
$H$249.875The value in H2.
B2>$H$2FALSEThe > operator tests greater than.
OR(B2<$G$2,B2>$H$2)FALSEOR: TRUE if any argument is true.- logical1
B2<$G$2FALSE - logical2
B2>$H$2FALSE
- logical1
IF(OR(B2<$G$2,B2>$H$2),"Outlier","")""IF: Returns one value if a test is true, another if false.- logical_test
OR(B2<$G$2,B2>$H$2)FALSE - value_if_true
"Outlier"not used - value_if_false
""
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(OR(B2<$G$2,B2>$H$2),"Outlier",""). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(ABS(B2-MEDIAN($B$2:$B$11))>3*STDEV($B$2:$B$11),"Outlier","") | , , …Different result | A standard-deviation rule instead of the IQR rule. Marks values more than 3 standard deviations from the median, which can disagree with the IQR fences on small data sets. |
Common mistakes
- Forgetting
$on the fence cells makes them slide down as you fill. - An outlier is a prompt to check the data, not a reason to delete it. Confirm whether it is an error or a real event first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find outliers with the IQR method
C2, show Outlier when the minutes are below the lower fence in G2 or above the upper fence in H2. Fill down to C11.Questions
Why 1.5?
It is the conventional multiplier from Tukey's box plot rule. Use 3 for "extreme" outliers only.
Checked by running on 2026-11-21: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.