What formula finds outliers in Excel?

For the Z-score method: =(A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100), then flag any result whose absolute value exceeds 3. For the IQR method, compute QUARTILE.INC for Q1 and Q3 and flag values outside Q1 − 1.5×IQR and Q3 + 1.5×IQR.

Which outlier method is most reliable?

The IQR method is more robust for skewed or small datasets because it isn't distorted by the very extreme values it's trying to detect. The Z-score method works best on roughly normal data.

How do I highlight outliers automatically?

Use Conditional Formatting → New Rule → 'Use a formula', and enter the Z-score or IQR test. Excel will shade any cell that meets the condition.

AnalyticsExcelStatistics· 7 min read · Updated July 27, 2026

How to Find Outliers in Excel: 3 Proven Methods

Quick answer

The two most reliable ways to find outliers in Excel are the Z-score method (flag values more than 3 standard deviations from the mean) and the IQR method (flag values outside Q1 − 1.5×IQR to Q3 + 1.5×IQR). Use conditional formatting to highlight them automatically.

By The Excel AI App Team

An outlier is a value that sits far from the rest of your data. It might be a typo, a fraudulent transaction, or a genuine extreme event — but you can’t decide until you find it. Here are the three methods analysts actually use in Excel, from most robust to quickest.

In a hurry? Paste a column into our free outlier finder and it flags anomalies with both the Z-score and IQR methods instantly — no formulas required.

Method 1: The Z-score method

The Z-score tells you how many standard deviations a value is from the mean. In a roughly normal dataset, almost everything falls within ±3 standard deviations, so anything beyond that is suspicious.

Put this in a helper column next to your data:

=(A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100)

Then flag any row where ABS(z) > 3. Lower the threshold to 2 for stricter screening. The Z-score method is simple but sensitive: a single huge outlier inflates the standard deviation and can hide smaller ones.

Method 2: The IQR (box-plot) method

The interquartile range method is more robust because it’s based on quartiles, not the mean. It’s the exact rule Excel box-and-whisker charts use.

Q1 =QUARTILE.INC($A$2:$A$100, 1)
Q3 =QUARTILE.INC($A$2:$A$100, 3)
IQR = Q3 - Q1

Any value below Q1 - 1.5*IQR or above Q3 + 1.5*IQRis an outlier. Because quartiles ignore the extremes themselves, this method isn’t fooled by the very values it’s hunting — making it the better default for skewed or small datasets.

Method 3: Highlight them automatically with conditional formatting

Rather than reading a helper column, shade outliers in place:

Select your data range.
Home → Conditional Formatting → New Rule → “Use a formula to determine which cells to format”.
Enter the test, e.g. =ABS((A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100))>3.
Pick a fill color and click OK.

Which method should you use?

Use the IQR method as your default, especially for skewed data. Use the Z-scorewhen your data is roughly normal and you want a familiar “3 sigma” rule. When they disagree, inspect the flagged rows by hand before deleting anything.

For workbooks with many columns, checking each one manually gets tedious. Excel AI App scans an entire file for anomalies at once and lets you tune the threshold with a slider. See our AI spreadsheet tools comparison.