Z-score in Excel: how to find outliers

How to calculate a z-score in Excel

=STANDARDIZE(x, mean, standard_dev) takes the value, the mean and the standard deviation. Calculate the mean and standard deviation once, in their own cells, and refer to them with $ signs so the references stay fixed when you fill down: =STANDARDIZE(B2,$E$16,$E$17) with =AVERAGE(B2:B13) in E16 and =STDEV.S(B2:B13) in E17. The result is identical to =(B2-$E$16)/$E$17; STANDARDIZE just makes the intention clearer.

Use STDEV.S when the data are a sample, which is the usual case, and STDEV.P only when you treat the values as the complete population (see STDEV.S vs STDEV.P). A z-score is unit-free: a z of 2 means "two standard deviations above average" whether the data are euros, minutes or kilograms. That is why z-scores are also used to put different measures on one scale before combining them into a score, for example in customer segmentation or employee calibration.

Step by step: flag the outliers

1. Mean and standard deviation. 146.58 and 64.99 for the twelve orders. 2. Z-scores. Fill STANDARDIZE down column C. Most orders land between −0.6 and −0.2, because the two expensive orders pull the mean above them. 3. Flag. =IF(ABS(C2)>2,"Outlier","") marks every value more than two standard deviations away in either direction; ABS makes the rule catch unusually low values too. 4. Count. =COUNTIF(D2:D13,"Outlier") returns 1.

Which threshold? For roughly normal data, about 95% of the values lie within ±2 standard deviations and about 99.7% within ±3. A cut-off of 2 therefore flags about 1 value in 20 even when nothing is wrong, while 3 flags only extreme values but can miss real problems in small data sets. Treat a flag as "look at this record", not as "delete this record".

Why z-scores miss outliers in small data sets

The outliers themselves enter the mean and the standard deviation. A single extreme value raises the standard deviation so much that its own z-score shrinks, an effect called masking. Without the two unusual orders the other ten average 122.1 with a standard deviation of only about 7; with them the standard deviation jumps to 65, and the 198 order looks completely normal with z = 0.79.

There is also a hard ceiling. With n values, no z-score based on the sample standard deviation can exceed (n − 1) ÷ √n. For n = 12 that is 3.18, so a rule of |z| > 3 can almost never fire, however wrong a value is. With 10 values or fewer, |z| > 3 is mathematically impossible. In small data sets, use a lower threshold, a method based on quartiles or the median, or remove the most extreme value and recalculate.

Worked example: twelve delivery costs, z-scores vs IQR fences

Delivery costs per order (yellow). Column C is the z-score, D flags |z| above 2, E flags values outside the IQR fences. The summary shows why the two methods disagree about order #1012.

Worked example: twelve delivery costs, z-scores vs IQR fences

	A	B	C	D	E
1	Order	Delivery cost	Z-score	|z| > 2?	Outside IQR fences?
2	#1001	118	-0.44
3	#1002	124	-0.35
4	#1003	131	-0.24
5	#1004	109	-0.58
6	#1005	127	-0.30
7	#1006	122	-0.38
8	#1007	340	2.98	Outlier	Outlier
9	#1008	115	-0.49
10	#1009	129	-0.27
11	#1010	120	-0.41
12	#1011	126	-0.32
13	#1012	198	0.79		Outlier
14
15	Summary				Value
16	Mean (AVERAGE)				146.58
17	Standard deviation (STDEV.S)				64.99
18	Q1 (QUARTILE.INC, 1)				119.50
19	Q3 (QUARTILE.INC, 3)				129.50
20	Lower fence = Q1 − 1.5 × IQR				104.50
21	Upper fence = Q3 + 1.5 × IQR				144.50
22	Outliers by z-score				1
23	Outliers by IQR fences				2
24	Largest possible |z| with n = 12: (n − 1) ÷ √n				3.18
25	Mean without the outlier (AVERAGEIFS)				122.10
26	Median, barely moved by the outlier (MEDIAN)				125.00

Illustrative data. An outlier flag is a prompt to check a record, not proof that it is wrong.

The IQR fence method as an alternative

The interquartile range method uses the quartiles, which the outliers hardly affect. =QUARTILE.INC(B2:B13,1) returns Q1 = 119.5 and =QUARTILE.INC(B2:B13,3) returns Q3 = 129.5, so the IQR is 10. The fences are Q1 − 1.5 × IQR = 104.5 and Q3 + 1.5 × IQR = 144.5, the same rule a box plot uses for its whiskers. Both 340 and 198 lie above the upper fence. Use 3 × IQR instead of 1.5 to flag only extreme outliers.

After finding outliers, decide what to do with them case by case: correct data-entry errors, keep genuine but unusual cases, and report both versions when in doubt. Two useful summary numbers are the mean without the flagged values, =AVERAGEIFS(B2:B13,E2:E13,"") = 122.1, and the median, =MEDIAN(B2:B13) = 125, which would stay exactly the same if the 340 order had cost a normal 130. For skewed data such as prices, incomes or response times, the median and the IQR method are usually the better choice; z-scores work best on data that are roughly symmetric.

Common mistakes

• Relative references for the mean and SD. Without $ signs the mean and standard deviation cells shift as you fill down and each z-score uses the wrong values.
• Using |z| > 3 on a small data set. With 12 values the largest possible z is 3.18, and with 10 or fewer it can never exceed 3. The rule then finds nothing.
• Forgetting ABS. =IF(C2>2,…) only finds unusually high values. Very low values have large negative z-scores.
• Ignoring masking. One extreme value inflates the standard deviation and hides other outliers. Check with the IQR method or recalculate without the extreme value.
• Deleting every flagged value. An outlier can be a typo or the most important record in the file. Investigate before removing anything, and document what you removed.
• Applying z-scores to heavily skewed data. For prices or waiting times with a long right tail, many normal values get high z-scores. Use quartiles or a log scale instead.