How to Calculate Z-Scores in Google Sheets

August 7th, 2026

A z-score answers one question: how many standard deviations is this value from the mean? Positive means above average, negative below, and magnitude tells you how unusual the point is. In Google Sheets you can build z-scores with STANDARDIZE or the plain formula (x − mean) / stdev, using AVERAGE and STDEV.S (or STDEV.P) for the inputs.

The formula behind the function

For a value x, sample mean x̄, and sample standard deviation s:

z = (x − x̄) / s

In a cell:

=(A2 - AVERAGE($A$2:$A$100)) / STDEV.S($A$2:$A$100)

Or with the dedicated function:

=STANDARDIZE(A2, AVERAGE($A$2:$A$100), STDEV.S($A$2:$A$100))

Both return the same number. Prefer STANDARDIZE when you already store mean and SD in cells; prefer the explicit form when you want every piece visible.

STANDARDIZE — one value, known mean and SD

STANDARDIZE takes three arguments:

=STANDARDIZE(value, mean, standard_deviation)

=STANDARDIZE(75, 80, 5)

Returns -1: 75 is one standard deviation below a mean of 80.

=STANDARDIZE(92, 80, 5)

Returns 2.4: well above the center of that distribution.

AVERAGE + STDEV.S on a real range

Put scores (or sales, times, measurements) in A2:A20. Compute summary stats once:

=AVERAGE(A2:A20)
=STDEV.S(A2:A20)

Use STDEV.S when the range is a sample (most spreadsheet work). Use STDEV.P only when the range is the entire population. Mixing the two changes every z-score slightly.

Then for each row:

=STANDARDIZE(A2, $B$1, $B$2)

with mean in B1 and sample SD in B2. Lock the summary cells with $ so fill-down stays correct.

ARRAYFORMULA for a whole column

To spill z-scores for an entire list without copying a formula per row:

=ARRAYFORMULA(IF(A2:A="",, STANDARDIZE(A2:A, AVERAGE(A2:A), STDEV.S(A2:A))))

Or without STANDARDIZE:

=ARRAYFORMULA(IF(A2:A="",, (A2:A - AVERAGE(A2:A)) / STDEV.S(A2:A)))

Empty rows stay blank thanks to the IF. Note that AVERAGE/STDEV.S over A2:A ignore blanks but treat zeros as data — clean the column first if zeros mean "missing."

Interpreting z-scores (and spotting outliers)

Under a roughly normal distribution:

| |z| | Rough meaning | | --- | --- | | 0 | Exactly at the mean | | ~1 | Typical (about 68% of data within ±1) | | ~2 | Unusual (about 95% within ±2) | | ~3 | Rare (about 99.7% within ±3) |

A common practical rule: flag |z| > 2 as candidates for review, and |z| > 3 as strong outliers. That is a heuristic, not a law — skewed data and small samples need judgment.

=IF(ABS(B2)>2, "review", "ok")

If column B holds z-scores, this labels the tails. For conditional formatting, use a custom formula on the raw values:

=ABS(STANDARDIZE(A2, AVERAGE($A$2:$A$100), STDEV.S($A$2:$A$100)))>2

Comparing different scales

Z-scores shine when units differ. Exam score out of 100 and response time in seconds are not comparable as raw numbers; as z-scores they share a common "distance from typical" scale. Standardize each series with its own mean and SD, then compare or average the z's.

=STANDARDIZE(A2, AVERAGE($A$2:$A$50), STDEV.S($A$2:$A$50))
=STANDARDIZE(B2, AVERAGE($B$2:$B$50), STDEV.S($B$2:$B$50))

Common mistakes

• Dividing by zero. If every value is identical, SD is 0 and you get #DIV/0!. Check STDEV.S before standardizing.
• Sample vs population. STDEV alone is legacy; prefer explicit STDEV.S or STDEV.P.
• Using a global mean on filtered groups. Z-scores for "Team A only" need mean and SD computed on Team A, not the whole sheet.
• Treating z as a probability. The z-score is a distance; turn it into a probability with NORM.S.DIST if you need a tail probability under a normal model.

Which approach should you pick?

| Goal | Approach | | --- | --- | | One-off value, mean/SD known | STANDARDIZE(x, mean, sd) | | Column of values | (x-AVERAGE)/STDEV.S or STANDARDIZE + fill / ARRAYFORMULA | | Outlier flags | ABS(z)>2 (or >3) with IF or conditional formatting | | Tail probability (normal) | NORM.S.DIST on the z-score |

Going further

For broader stats in Sheets, see Understanding Statistical Functions in Google Sheets and Statistical Distributions and Hypothesis Testing in Google Sheets. References: STANDARDIZE, AVERAGE, STDEV.S, STDEV.P.