# How to find outliers using standard deviation in Excel

Flag values more than two standard deviations from the mean: `=IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"Outlier","")`.

**Excel:** `=IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"Outlier","")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Date | Orders | Flag |
| 2026-05-04 | 212 |  |
| 2026-05-05 | 198 |  |
| 2026-05-06 | 225 |  |
| 2026-05-07 | 204 |  |
| 2026-05-08 | 219 |  |
| 2026-05-09 | 611 |  |
| 2026-05-10 | 207 |  |
| 2026-05-11 | 201 |  |
| 2026-05-12 | 215 |  |
| 2026-05-13 | 209 |  |

Formula in C2, filled to C11: `=IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"Outlier","")`

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

## How it works

The mean is about 250 orders and the standard deviation about 127. May 9's 611 orders are 361 above the mean, more than twice the standard deviation, so they are flagged. Every other day is within about 50 of the mean. ABS makes unusually low days count too.

## Other ways

- `=IF(ABS((B2-AVERAGE($B$2:$B$11))/STDEV.S($B$2:$B$11))>2,"Outlier","")`: The same test written as a z-score above 2.

## Common mistakes

- The outlier inflates the mean and standard deviation it is measured against. In small lists a big outlier can hide a smaller one; the interquartile-range method is more robust.
- Two standard deviations is a convention, not a rule. Use 3 for stricter flagging on large data.

## Questions

**How do I use the interquartile range instead?**

Flag values below `QUARTILE.INC(B2:B11,1)-1.5*IQR` or above `QUARTILE.INC(B2:B11,3)+1.5*IQR`, where IQR is Q3 − Q1.

**How do I highlight outliers instead of labeling them?**

Use the same test as a conditional formatting rule: `=ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11)`.

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

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