How to Detect Anomalies in Spreadsheet Data (IQR + Z-Score)
Detect anomalies in a spreadsheet two ways: the Z-score method flags values more than about 3 standard deviations from the mean, and the IQR method flags values outside Q1 − 1.5×IQR to Q3 + 1.5×IQR. The IQR method is more robust for skewed data; automated tools apply both across every column on upload.
An anomaly is a value that doesn’t fit the pattern of the rest — a typo, a fraud signal, or a genuine spike worth investigating. Two statistical methods catch most of them, and they’re both a few formulas away.
Method 1: Z-score
The Z-score is how many standard deviations a value sits from the mean:
=(A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100)
Flag any row where the absolute Z-score exceeds 3 (use 2 for stricter screening). It assumes your data is roughly normal, and — because the mean and standard deviation are themselves pulled by extremes — a few huge outliers can mask smaller ones.
Method 2: IQR (the box-plot rule)
Find the first and third quartiles, then the interquartile range:
Q1 =QUARTILE.INC(A2:A100,1) · Q3 =QUARTILE.INC(A2:A100,3) · IQR = Q3 − Q1
Any value below Q1 − 1.5×IQR or above Q3 + 1.5×IQRis an outlier. Because quartiles ignore the extreme tails, IQR isn’t distorted by the very values it’s hunting — which is why it’s the safer default for messy, skewed data.
Want to try it on your own numbers right now? Paste a column into the free outlier & anomaly finder — it reports both methods at once.
Detecting anomalies across a whole workbook
Formula-by-formula screening is fine for one column and painful for fifty. Excel AI App runs a Z-score scan on every numeric column automatically when you upload a file and lets you tune the threshold with a slider, so anomalies surface without manual setup. For the hand method on a single column, see how to find outliers in Excel.
What to do with a flagged value
- Investigate before deleting — an outlier can be an error to fix or a real event to keep.
- Check the source row for a data-entry mistake (a misplaced decimal is the classic).
- If it’s genuine, decide whether your analysis should include or exclude it, and say which.