How to Use the STANDARDIZE Function in Google Sheets to Calculate Z-Scores and Normalise Datasets

In data analysis, financial risk modeling, and academic grading, comparing raw values across different datasets is challenging when scales, units, or variances differ. For instance, comparing an exam score of 78 out of 100 on a difficult test against an 88 on an easy test requires standardising both datasets into standard deviations from their respective means. This mathematical normalization process is called calculating the z-score. Google Sheets provides a dedicated statistical formula for this purpose: the STANDARDIZE function.

Mathematical Definition and Z-Score Concept

A z-score measures exactly how many standard deviations an individual observation ($x$) lies above or below the mean ($\mu$) of its population:

z = (x - mean) / standard_deviation

• Z = 0: Indicates the raw score is identical to the arithmetic mean.
• Positive Z-Score: Indicates a score above the group mean (e.g. +1.5 means 1.5 standard deviations above average).
• Negative Z-Score: Indicates a score below the group mean (e.g. -2.0 means 2 standard deviations below average).
• Outlier Identification: In normal Gaussian distributions, scores exceeding +3.0 or below -3.0 are statistically rare (representing less than 0.3% of values) and are classified as potential anomalies or outliers.

Syntax of the STANDARDIZE Function

The STANDARDIZE formula takes three straightforward arguments:

=STANDARDIZE(value, mean, standard_deviation)

Argument	Required / Optional	Data Type	Description
value	Required	Number / Cell	The individual data point you want to standardise (e.g. A2).
mean	Required	Number / Cell	The arithmetic mean ($\mu$) of the distribution, calculated via AVERAGE.
standard_deviation	Required	Number / Cell	The standard deviation ($\sigma$) of the distribution, calculated via STDEV.S (sample) or STDEV.P (population). Must be greater than zero.

Step-by-Step Practical Z-Score Calculation

Suppose you have a list of employee quarterly sales volumes in column A from cells A2 to A21:

1. Calculate the dataset mean in a dedicated summary cell (e.g. cell D1):

   =AVERAGE(A2:A21)

2. Calculate the sample standard deviation in cell D2:

   =STDEV.S(A2:A21)

3. In cell B2, calculate the standardised z-score for the first employee using absolute cell references for the mean and standard deviation:

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

4. Press Enter.
5. Double-click the blue fill handle at the corner of cell B2 to drag the formula down the entire column alongside all sales figures.

Dynamic Single-Cell Array Formulation

If you prefer an automated single-formula approach that dynamically populates the entire column without manually dragging formulas down, combine STANDARDIZE with MAP and LAMBDA:

=MAP(A2:A21, LAMBDA(x, STANDARDIZE(x, AVERAGE(A2:A21), STDEV.S(A2:A21))))

This array formula calculates the mean and standard deviation once in memory, dynamically spilling normalised z-scores down the entire target range.

Handling Common Formula Errors

• #NUM! Error: Occurs if the supplied standard_deviation is less than or equal to zero. If all values in your raw column are identical, variance is zero, making division impossible.
• #VALUE! Error: Occurs if non-numerical text strings or headers are inadvertently passed into the formula arguments. Ensure your raw column contains only numerical data or use ISNUMBER filtering.