Spreadsheet Practice

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 to C11
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""

  1. B234

    The value in B2.

  2. $G$224.875

    The value in G2.

  3. B2<$G$2FALSE

    The < operator tests less than.

  4. $H$249.875

    The value in H2.

  5. B2>$H$2FALSE

    The > operator tests greater than.

  6. OR(B2<$G$2,B2>$H$2)FALSE

    OR: TRUE if any argument is true.

    • logical1B2<$G$2FALSE
    • logical2B2>$H$2FALSE
  7. IF(OR(B2<$G$2,B2>$H$2),"Outlier","")""

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

    • logical_testOR(B2<$G$2,B2>$H$2)FALSE
    • value_if_true"Outlier"not used
    • value_if_false""

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.

FormulaResultWhen to use it
=IF(ABS(B2-MEDIAN($B$2:$B$11))>3*STDEV($B$2:$B$11),"Outlier",""), , …Different resultA 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

In 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-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.