Spreadsheet Practice

How to find outliers using standard deviation in Excel

Formula (Excel and Google Sheets)

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

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","").

Result on the example
one result per row in C2, filled down to C11
Works in
Every current version
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 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.

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

Result""

  1. B2212

    The value in B2.

  2. $B$2:$B$1110 rows × 1 col: 212, 198, 225, 204, …

    A range: 10 cells in one column.

  3. AVERAGE($B$2:$B$11)250.1

    AVERAGE: Arithmetic mean of the numbers.

    • number1$B$2:$B$1110 rows × 1 col: 212, 198, 225, 204, …
  4. B2-AVERAGE($B$2:$B$11)-38.1

    The - operator subtracts.

  5. ABS(B2-AVERAGE($B$2:$B$11))38.1

    ABS: Absolute value.

    • numberB2-AVERAGE($B$2:$B$11)-38.1
  6. STDEV.S($B$2:$B$11)127.0725515

    STDEV: Sample standard deviation (same as STDEV.S).

    • number1$B$2:$B$1110 rows × 1 col: 212, 198, 225, 204, …
  7. 2*STDEV.S($B$2:$B$11)254.1451029

    The * operator multiplies.

  8. ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11)FALSE

    The > operator tests greater than.

  9. IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"Outlier","")""

    IF: Returns one value if a test is true, another if false.

    • logical_testABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11)FALSE
    • value_if_true"Outlier"not used
    • value_if_false""

In Google Sheets

The same formula works in Google Sheets: =IF(ABS(B2-AVERAGE($B$2:$B$11))>2*STDEV.S($B$2:$B$11),"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.

FormulaResultWhen to use it
=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.

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 using standard deviation

In C2, show "Outlier" if the day's orders are more than two standard deviations from the mean, otherwise leave it empty. Fill down to C11.

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

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.