STANDARDIZE Function

Returns a z-score: how many standard deviations a value is from the mean.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Want to practice STANDARDIZE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

STANDARDIZE calculates (x − mean) ÷ standard deviation. The result, a z-score, puts values measured on different scales onto one common scale.

0 means exactly average, +1 means one standard deviation above, −2 two below. In bell-shaped data, about 95% of values fall between −2 and +2.

You supply the mean and standard deviation yourself — usually with AVERAGE and STDEV.S.

Syntax

=STANDARDIZE(x, mean, standard_dev) Explain

Arguments

ArgumentRequired?What it does
xYesThe value to standardize.
meanYesThe average of the data.
standard_devYesThe standard deviation of the data; must be greater than 0.

Examples

Example 1 — A z-score for every rep

Lock the mean and standard deviation ranges with $ so the formula fills down.

D2fx=STANDARDIZE(C2,AVERAGE($C$2:$C$8),STDEV.S($C$2:$C$8))
ABCD
1RepRegionSalesz-score
2AnaNorth620000.81
3BenSouth48500-0.50
4CyNorth50000-0.35
5DiEast712501.70
6EdSouth39800-1.34
7FayNorth554000.17
8GusEast48500-0.50
=STANDARDIZE(C2,AVERAGE($C$2:$C$8),STDEV.S($C$2:$C$8)) Explain

Di is 1.7 standard deviations above average; Ed is 1.34 below. Nobody is beyond ±2.

Example 2 — Comparing different scales

A 78 on a test with mean 70 and standard deviation 4 beats an 85 on a test with mean 80 and standard deviation 10.

=STANDARDIZE(78,70,4) Explain

Returns 2, while =STANDARDIZE(85,80,10) returns only 0.5.

Common mistakes

  • #NUM! standard_dev is 0 or negative.
  • Relative ranges. Without $ signs, the mean and standard deviation ranges shift as you fill down and every z-score is wrong.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.