Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find outliers in a dataset in Excel and Google Sheets
Topic:RANK & statistical aggregatesExcel & Google Sheets
=IF(OR(B2<QUARTILE($B$2:$B$6,1)-1.5*(QUARTILE($B$2:$B$6,3)-QUARTILE($B$2:$B$6,1)),B2>QUARTILE($B$2:$B$6,3)+1.5*(QUARTILE($B$2:$B$6,3)-QUARTILE($B$2:$B$6,1))),"Outlier","Normal")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your data in column B (e.g., B2:B6). Lock the range with $ signs.
- 2In column C2, enter the IQR outlier formula using QUARTILE to compute Q1, Q3, and IQR.
- 3The formula flags values below Q1-1.5*IQR or above Q3+1.5*IQR as 'Outlier'.
- 4Copy the formula down column C for every data row.
Tips
- For a simpler threshold approach, flag values more than 2 standard deviations from the mean: =IF(ABS(B2-AVERAGE($B$2:$B$6))>2*STDEV($B$2:$B$6),"Outlier","Normal").
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why use 1.5 times the IQR?
This is Tukey's fence rule, a widely accepted statistical convention for defining mild outliers. Use 3*IQR for extreme outliers.
Can I highlight outliers with conditional formatting?
Yes. Apply conditional formatting to the data column using the same IQR formula as the rule, and choose a highlight color.
More on RANK & statistical aggregates
See all →Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
How-toHow to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
ReferenceRANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
How-toHow to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
ReferencePERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-toHow to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.