# How to find outliers with the IQR method in Excel

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.

**Excel:** `=IF(OR(B2<$G$2,B2>$H$2),"Outlier","")` (Excel 2010 and later)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Order | Minutes | Flag |  | Q1 | Q3 | Lower fence | Upper fence |
| #1 | 34 |  |  | =QUARTILE.INC(B2:B11,1) | =QUARTILE.INC(B2:B11,3) | =E2-1.5*(F2-E2) | =F2+1.5*(F2-E2) |
| #2 | 38 |  |  |  |  |  |  |
| #3 | 41 |  |  |  |  |  |  |
| #4 | 29 |  |  |  |  |  |  |
| #5 | 36 |  |  |  |  |  |  |
| #6 | 44 |  |  |  |  |  |  |
| #7 | 39 |  |  |  |  |  |  |
| #8 | 31 |  |  |  |  |  |  |
| #9 | 95 |  |  |  |  |  |  |
| #10 | 35 |  |  |  |  |  |  |

Formula in C2, filled to C11: `=IF(OR(B2<$G$2,B2>$H$2),"Outlier","")`

Result: ; ; ; ; ; ; ; ; Outlier; 

## 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.

## Other ways

- `=IF(ABS(B2-MEDIAN($B$2:$B$11))>3*STDEV($B$2:$B$11),"Outlier","")`: 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.

## Questions

**Why 1.5?**

It is the conventional multiplier from Tukey's box plot rule. Use 3 for "extreme" outliers only.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-11-20.

Source: https://spreadsheetpractice.com/formulas/find-outliers-with-iqr
