STANDARDIZE in Google Sheets: Z-Scores and Reference Ranges

=STANDARDIZE(value, mean, standard_deviation)

All three arguments are required. value is the observation to rescale; mean is the reference center; standard_deviation is the reference spread. Google documents these arguments in its STANDARDIZE reference and points to AVERAGE and STDEV for estimating them from data.

The calculation is the z-score (value - mean) / standard_deviation. Use a positive standard deviation. A zero spread cannot provide a denominator for this normalization, and a negative number is not a valid standard deviation.

STANDARDIZE does not decide which data belong in the reference population. It also does not choose between sample and population estimates. Those decisions determine the inputs you provide.

Enter this table in A1:C4:

Put Z-score in D1. Enter this in D2 and fill down to D4:

=STANDARDIZE(A2,B2,C2)

Expected D2:D4: 1.5, −2, 0.

• 72 is 12 above the mean, and 12/8 = 1.5.
• 44 is 16 below the mean, and −16/8 = −2.
• An observation equal to its mean has z = 0.

Enter =(A2-B2)/C2 in E2 as an independent arithmetic cross-check: expected 1.5. The explicit parentheses matter. =A2-B2/C2 follows a different order of operations and has expected result 64.5, not the z-score.

A z-score measures relative distance in standard-deviation units. It can be negative or larger than 1. It is not a probability, percentile or percentage, so format the result as a regular number.

Standardizing a variable does not make its distribution normal. A skewed set remains skewed after a positive linear rescaling. Using a normal probability distribution to turn z into a percentile requires an additional justified model.

For a fixed mean and standard deviation, you can reverse the transformation. With z = 1.5, the original value is 60+1.5*8, or 72. This is useful when a report uses standardized scores but an operational threshold must be stated in the original units.

Use a fresh sheet for this example. Put Measurement in A1 and the numeric values 40, 50, 60, 70, 80 in A2:A6. Put the following formulas in E2 and E3:

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

Expected mean E2: 60. Expected sample standard deviation E3: approximately 15.8113883008. The squared deviations total 1000; the sample variance is 1000/(5−1) = 250.

Enter this in B2 and fill through B6:

=STANDARDIZE(A2,$E$2,$E$3)

The dollar signs freeze the benchmark cells while each measurement reference advances. A formula that allows E2 and E3 to move during fill can silently use the wrong center and spread.

For this complete numeric sample, the standardized scores have mean 0 and sample standard deviation 1, subject to numerical precision. These are checks of the transformation with the same reference data and the same sample convention.

If those five measurements are the entire population being described, put =STDEV.P(A2:A6) in E4. Expected result: 14.1421356237, from √(1000/5).

=STANDARDIZE(80,$E$2,$E$4)

Expected result: 1.4142135624, compared with the sample-based 1.2649110641. Neither difference is a formatting issue: the spread estimator changed.

Google’s catalogue maps STDEV.S to sample STDEV and STDEV.P to population STDEVP. Choose the convention required by the analysis, then use it consistently. Do not replace a documented benchmark standard deviation with a newly estimated one just because both are available.

1. No variation: If every reference measurement is identical, standard deviation is zero and standardization is undefined. Return a deliberate missing-result message under your reporting policy rather than inventing z = 0 for all future observations.
2. Wrong units: The observation, mean and standard deviation must use the same unit. Converting all three consistently leaves the z-score unchanged; converting only one does not.
3. Wrong reference group: A score calculated against this month’s data is different from one using a historical benchmark. Label the benchmark and freeze it if comparison over time is intended.
4. Changing range after fill: Lock shared references and exclude headings from numeric ranges.
5. Missing or text values: Decide which observations belong in the sample before computing its mean and spread. Review AVERAGE and the chosen standard-deviation function’s data rules.

The direct STANDARDIZE help does not enumerate exact native errors for nonpositive spread, text or blank inputs. No error-token mapping or coercion rule is borrowed here from Excel or another engine. For a rejected formula, inspect the numeric inputs and locale separators before nesting error handlers.

STANDARDIZE is documented for the current rolling Google Sheets service. The reviewed references do not establish the original release date, an introduction version or a last functioning numbered release. This guide covers ordinary worksheet formulas.

Evidence status: NOT_TESTED_IN_GOOGLE_SHEETS. Google documentation was inspected on October 17, 2026. Means, sample and population spreads, and every worked z-score were calculated independently. No native Sheets session or screenshot was used. A migration should compare the reference population and spread convention as well as the function spelling.

Related published guides: AVERAGE, STDEV.S, STDEV.P and SQRT.