How to Find Outliers in Excel
(The Step-by-Step IQR Method)
Excel doesn't have a "Find Outliers" button. But you can build your own detector using the Interquartile Range (IQR) method. Here is exactly how to do it.
The Short Answer (Formulas)
To find outliers in a dataset (e.g., cells A2:A20):
- Q1: =QUARTILE.INC(A2:A20, 1)
- Q3: =QUARTILE.INC(A2:A20, 3)
- IQR: =Q3 - Q1
- Lower Limit: =Q1 - (1.5 * IQR)
- Upper Limit: =Q3 + (1.5 * IQR)
Any value lower than the Lower Limit or higher than the Upper Limit is an outlier.
Method 1: The Manual Excel Way
This method uses the standard Tukey Fence definition of outliers (1.5 * IQR). It requires setting up a few helper cells.
Step 1: Calculate Quartiles
First, you need to find the 25th percentile (Q1) and the 75th percentile (Q3).
=QUARTILE.INC(Range, 3) // For Q3
Note: Excel has both .INC (inclusive) and .EXC (exclusive) functions. .INC is generally the default for most business applications.
Step 2: Calculate IQR
The Interquartile Range (IQR) is the spread of the middle 50% of your data.
Step 3: Define Limits
Calculate the "fences" beyond which data points are considered outliers.
Upper Fence = Q3 + (1.5 * IQR)
Step 4: Highlight Outliers
Use Conditional Formatting to visually identify the outliers in your list.
- Select your data range.
- Go to Home > Conditional Formatting > New Rule.
- Choose "Use a formula to determine which cells to format".
-
Enter formula:
=OR(A2<LowerLimitCell, A2>UpperLimitCell) - Set a format (e.g., Red Fill).
Method 2: The Faster Way (PlotNerd)
Why write 5 formulas when you can just paste your data? PlotNerd calculates outliers instantly and visualizes them.
Excel Workflow π’
- β Write Q1 formula
- β Write Q3 formula
- β Calculate IQR
- β Calculate Limits
- β Set Conditional Formatting
PlotNerd Workflow π
- β Paste Data
- β Done.
No signup required. Free forever.
Common Questions
Should I use QUARTILE.INC or QUARTILE.EXC?
For most general datasets, QUARTILE.INC is safer as it works with smaller datasets. QUARTILE.EXC excludes the 0th and 100th percentiles and can return errors if your dataset is too small. PlotNerd uses a standard method compatible with most statistical definitions.
Does Excel have a built-in outlier function?
No, Excel does not have a single function like =ISOUTLIER(). You must manually construct the logic using the
IQR method or use a specialized add-in.