How to Use the STANDARDIZE Function in Google Sheets to Calculate Statistical Z-Scores

In quantitative finance, machine learning feature engineering, and statistical data analysis, comparing variables measured across different units—such as employee salary in pounds versus years of experience, or server CPU utilisation versus disk latency—presents a fundamental scaling challenge. Because raw numbers exist on vastly different numerical scales, standard deviation comparisons and regression algorithms become heavily biased toward variables with larger nominal values.

To eliminate unit bias and establish statistical parity, analysts standardize data by calculating Z-scores (standard scores). A Z-score quantifies exactly how many standard deviations a raw data point lies above or below the population mean. In Google Sheets, this mathematical transformation is handled natively by the STANDARDIZE function. This guide explains its mathematical formula, syntax structure, and real-world spreadsheet implementation.

Understanding Z-Score Mathematics

The standard formula for calculating a Z-score is:

z = (x - mean) / standard_deviation

• Z = 0: The raw value is exactly equal to the dataset mean.
• Z > 0: The raw value lies above the mean (e.g. +1.5 indicates 1.5 standard deviations above average).
• Z < 0: The raw value lies below the mean (e.g. -2.0 indicates 2.0 standard deviations below average).
• Empirical Rule (68-95-99.7): Approximately 68% of normally distributed data falls within |Z| <= 1, 95% within |Z| <= 2, and 99.7% within |Z| <= 3. Values with |Z| > 3 are statistically classified as extreme outliers.

Syntax and Parameter Requirements

The syntax for STANDARDIZE in Google Sheets is:

=STANDARDIZE(x, mean, standard_dev)

Parameter Definitions

• x: The individual data value to standardize.
• mean: The arithmetic mean (average) of the dataset (typically computed via AVERAGE).
• standard_dev: The standard deviation of the dataset (computed via STDEV.S for samples or STDEV.P for populations; must be strictly > 0).

Step-by-Step Practical Implementation

Suppose you evaluate the performance of eight regional sales representatives. Monthly revenue numbers vary significantly, and management wants to identify high performers and outliers using standardized Z-scores:

A1: Sales Rep   | B1: Revenue (£) | C1: Z-Score
A2: Sarah Brown | B2: 45,000      | C2: =STANDARDIZE(B2, $B$10, $B$11)
A3: David Clark | B3: 38,000      | C3: =STANDARDIZE(B3, $B$10, $B$11)
A4: Emma Wilson | B4: 52,000      | C4: =STANDARDIZE(B4, $B$10, $B$11)
A5: James Taylor| B5: 29,000      | C5: =STANDARDIZE(B5, $B$10, $B$11)
A6: Oliver Hall | B6: 74,000      | C6: =STANDARDIZE(B6, $B$10, $B$11)
A7: Maya Patel  | B7: 41,000      | C7: =STANDARDIZE(B7, $B$10, $B$11)
A8: Liam Evans  | B8: 36,000      | C8: =STANDARDIZE(B8, $B$10, $B$11)
A9: Lucas White | B9: 43,000      | C9: =STANDARDIZE(B9, $B$10, $B$11)

A10: Mean       | B10: =AVERAGE(B2:B9)
A11: StDev      | B11: =STDEV.S(B2:B9)

Google Sheets evaluates the summary statistics: Mean = £44,750, StDev = £13,296. The formula calculates the following Z-scores:

• Sarah Brown (£45,000): +0.02 (Performing almost exactly at company average).
• James Taylor (£29,000): -1.18 (1.18 standard deviations below average).
• Oliver Hall (£74,000): +2.20 (Statistically exceptional performer; top 2.5% boundary).

Automating Array Normalization with MAP

Instead of copying formulas down individual rows, normalize an entire data column dynamically using modern Google Sheets array formulas:

=MAP(B2:B9, LAMBDA(val, STANDARDIZE(val, AVERAGE(B2:B9), STDEV.S(B2:B9))))

Troubleshooting Common Errors

• #NUM! Error: Occurs when standard_dev <= 0. If all values in your dataset are identical, the standard deviation is zero, causing division by zero. Ensure the dataset contains variance.
• #VALUE! Error: Triggered when non-numeric text strings or headers are included in numerical arguments.