Z-Scores: How Far From Average

Excel Formulas › Statistics

All versionsSTANDARDIZE

A z-score says how many standard deviations a value sits above or below the mean — turning raw numbers into a common scale you can compare and flag. STANDARDIZE computes it in one step.

Quick formula: for a value in A2, with the mean and stdev of the range:
=STANDARDIZE(A2, AVERAGE($A$2:$A$20), STDEV($A$2:$A$20))
Equivalent to (A2 − mean) / stdev. A z of +2 means “two standard deviations above average.”

Functions used (tap for the full reference guide):

STANDARDIZESTDEV

The example

Test scores standardized (mean 75, stdev 10).

AB
1ScoreZ-score
295+2.0
3750.0
460−1.5

The formula

Standardize a value against its distribution:

=STANDARDIZE(A2, AVERAGE($A$2:$A$20), STDEV($A$2:$A$20)) // 95 with mean 75, sd 10 → +2.0

How it works

The z-score re-centers and re-scales every value:

  1. Subtract the mean from the value — positive means above average, negative below.
  2. Divide by the standard deviation — now the unit is “standard deviations,” not the original scale.
  3. STANDARDIZE(x, mean, sd) does both at once. Lock the mean/stdev ranges so every row uses the same distribution.
  4. A z of 0 is exactly average; about 95% of normal data falls between −2 and +2, so |z| > 2 flags an unusual value.

Flag outliers fast: wrap it in a test — =IF(ABS(STANDARDIZE(A2,mean,sd))>2, "Outlier", ""). To turn a z-score back into a percentile, use =NORM.S.DIST(z, TRUE).

Try it: interactive demo

Live demo

Set a value, mean, and standard deviation.

Z-score:

Variations

Plain arithmetic

Same result, no function:

=(A2 - AVERAGE($A$2:$A$20)) / STDEV($A$2:$A$20)

Outlier flag

Mark |z| over 2:

=IF(ABS(STANDARDIZE(A2,$M$1,$S$1))>2, "Outlier", "")

Z back to percentile

Cumulative normal:

=NORM.S.DIST(zScore, TRUE)

Pitfalls & errors

Lock the mean and stdev. Use absolute references (or precomputed cells) so every value standardizes against the same distribution, not a shifting window.

Assumes a roughly normal shape. The “|z| > 2 is rare” rule of thumb relies on normality; for skewed data, prefer an IQR-based outlier test.

Zero stdev errors. If all values are identical, STDEV is 0 and the formula divides by zero.

Practice workbook

📊
Download the free Z-Scores: How Far From Average practice workbook
A z-score sheet with STANDARDIZE, the arithmetic, outlier-flag, and percentile variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a z-score in Excel?
Use =STANDARDIZE(value, mean, stdev), or equivalently (value-AVERAGE(range))/STDEV(range). It tells you how many standard deviations the value is from the mean.
What counts as an outlier by z-score?
A common rule is |z| > 2 (about 5% of normal data) or |z| > 3 for a stricter cutoff. Flag with =IF(ABS(z)>2,"Outlier","").
How do I turn a z-score into a percentile?
Use the cumulative standard normal: =NORM.S.DIST(z, TRUE) returns the proportion below that z-score.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Standard deviation · Coefficient of variation · Find outliers

Function references: STANDARDIZE · STDEV