Statistics function

Excel STANDARDIZE function

Q: How do I calculate a z-score in Excel?

Use =STANDARDIZE(x,AVERAGE(range),STDEV.S(range)), or the same thing written out: =(x-AVERAGE(range))/STDEV.S(range).

STANDARDIZE returns a z-score: how many standard deviations a value sits above or below the mean. A z-score of 2 means unusually high, -2 unusually low. You supply the mean and the standard deviation yourself, usually with AVERAGE and STDEV.S.

=STANDARDIZE(x, mean, standard_dev)

Arguments

x: The value to convert.
mean: The mean of the data, a single number such as AVERAGE($B$2:$B$11).
standard_dev: The standard deviation, a single number greater than 0, such as STDEV.S($B$2:$B$11).

STANDARDIZE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B
1	Day	Orders
2	Mon 2 Nov	120
3	Tue 3 Nov	135
4	Wed 4 Nov	128
5	Thu 5 Nov	142
6	Fri 6 Nov	118
7	Sat 7 Nov	210
8	Sun 8 Nov	125
9	Mon 9 Nov	131
10	Tue 10 Nov	138
11	Wed 11 Nov	122

Download this sheet as an Excel practice file (.xlsx)

=ROUND(STANDARDIZE(B7,AVERAGE($B$2:$B$11),STDEV.S($B$2:$B$11)),2)
Result: 2.72
Saturday’s 210 orders sit 2.72 standard deviations above the mean: a clear outlier.
=ROUND(STANDARDIZE(B2,AVERAGE($B$2:$B$11),STDEV.S($B$2:$B$11)),2)
Result: -0.63
Monday’s 120 orders are slightly below average, a z-score of -0.63.
=SUMPRODUCT(--(ABS(STANDARDIZE(B2:B11,AVERAGE(B2:B11),STDEV.S(B2:B11)))>2))
Result: 1
Counts days more than 2 standard deviations from the mean: 1, the Saturday spike.
=STANDARDIZE(B2,AVERAGE(B2:B11),0)
Result: #NUM!
A standard deviation of 0 or less returns #NUM!.

How to use STANDARDIZE

Put the mean and standard deviation in two cells, or write them inside the formula.
Type =STANDARDIZE(B2,AVERAGE($B$2:$B$11),STDEV.S($B$2:$B$11)).
Fill it down.
Flag values where ABS of the z-score is above 2 or 3.

Common STANDARDIZE mistakes

Passing the range as the mean

STANDARDIZE(B2,$B$2:$B$11,...) is wrong: the second argument is one number. Use AVERAGE($B$2:$B$11).

Forgetting the $ signs

Without $ the mean and standard deviation ranges shift as you fill down, and every z-score uses different data.

Trusting |z| > 3 on small data

One big outlier inflates the standard deviation it is measured against, so with ten values a z-score above 3 is hard to reach. Use 2, or the quartile rule.

STANDARDIZE FAQ

How do I calculate a z-score in Excel?

Use =STANDARDIZE(x,AVERAGE(range),STDEV.S(range)), or the same thing written out: =(x-AVERAGE(range))/STDEV.S(range).

Should I use STDEV.S or STDEV.P for z-scores?

Use STDEV.S when the data is a sample, which is most business data. Use STDEV.P only for a complete population.

What does a negative z-score mean?

The value is below the mean. -1 is one standard deviation below, -2 two below.

Guides that use STANDARDIZE

Related functions

Updated November 3, 2026