If you want to compare values measured on different scales, the STANDARDIZE function converts each value to a z-score using a mean and standard deviation.
In Excel 2021 and later, you can feed STANDARDIZE a range and the results spill into the cells below.
In this article, I’ll show you how to calculate z-scores, compare sample and population standard deviations, and flag values far from the mean.
STANDARDIZE Function Syntax in Excel
The STANDARDIZE function has three required arguments.
=STANDARDIZE(x,mean,standard_dev)
- x is the value you want to standardize.
- mean is the arithmetic mean of the distribution.
- standard_dev is the distribution’s standard deviation. It must be greater than zero.
The result equals (x - mean) / standard_dev. If you are new to the concept, this guide explains how to find a z-score in Excel.
Positive z-scores sit above the mean, negative scores sit below it, and zero equals the mean.
When to Use STANDARDIZE Function
- Express values as their distance from a mean in standard-deviation units.
- Compare observations from datasets that use different units or scales.
- Calculate z-scores from a known population mean and standard deviation.
- Calculate the mean and standard deviation from the source values before standardizing them.
- Flag observations that reach a chosen z-score threshold.
Example 1: Calculate Z-Scores from Known Metrics
We’ll begin with a mean and standard deviation that are already known.
Below is the dataset with student exam scores. The population mean is 76, and the population standard deviation is 8.
I want to standardize every exam score against those two population metrics.
Here is the formula in C2:
=STANDARDIZE(B2:B9,$F$2,$F$3)
The range B2:B9 supplies eight scores, so one formula spills through C9. The absolute references keep the mean and standard deviation fixed.
An exam score of 84 is one standard deviation above 76, so it returns 1.00. A score of 68 returns -1.00, and 76 returns 0.00.
The complete result is 1.00, -1.00, 0.00, 2.00, -0.50, 0.50, -2.00, and 1.50.
Pro Tip: Lock known metric cells with absolute references such as $F$2 and $F$3. Otherwise, those references can shift when you copy the formula.
Example 2: Calculate Population Z-Scores
This time, we’ll calculate the supporting metrics inside the formula.
Below is the dataset with the fill weight from all eight batches in a production run.
I want a population z-score for every batch, using the same eight values to calculate the mean and population standard deviation.
Here is the formula in C2:
=STANDARDIZE(B2:B9,AVERAGE(B2:B9),STDEV.P(B2:B9))
AVERAGE returns a mean of 16.00 ounces. STDEV.P treats the eight batches as the complete population and returns its population standard deviation.
STANDARDIZE applies both metrics to each weight. The results spill as -1.097, 0.305, -0.366, 0.671, -1.463, 1.097, -0.671, and 1.524.
Pro Tip: Use STDEV.P only when the listed values are the complete population you care about. Use STDEV.S when they are a sample from a larger population.
Example 3: Compare STDEV.P and STDEV.S
Here’s the same data standardized two ways.
Below is a sample of eight weekly commute times, with separate result columns for population and sample standard deviations.
I want to compare the z-scores produced by STDEV.P and STDEV.S for the same commute values.
Here is the population formula in C2:
=STANDARDIZE(B2:B9,AVERAGE(B2:B9),STDEV.P(B2:B9))
And here is the sample formula in D2:
=STANDARDIZE(B2:B9,AVERAGE(B2:B9),STDEV.S(B2:B9))
Both formulas use the same mean of 42.5 minutes. STDEV.S uses the sample method, which produces a larger standard deviation for this dataset.
That larger denominator moves the sample z-scores closer to zero. For 32 minutes, STDEV.P returns -0.972 while STDEV.S returns -0.909.
For 62 minutes, the two results are 1.805 and 1.688. Choose the standard-deviation function based on whether your values are a population or a sample.
Example 4: Flag Values at Least Two Standard Deviations Away
This example turns z-scores into a simple review flag. A threshold like this can help you find possible outliers for further investigation.
Below is the dataset with support-agent handle times. The population mean is 7.5 minutes, and the population standard deviation is 0.5 minutes.
I want to flag handle times whose z-score is at least 2 or at most -2.
Here is the formula in C2:
=IF(ABS(STANDARDIZE(B2:B9,$F$2,$F$3))>=2,"Review","Within range")
STANDARDIZE calculates the z-scores, and ABS removes their signs. That lets one >=2 test catch observations on either side of the mean.
IF returns “Review” for Olivia Carter at 8.7 minutes and Lucas Hayes at 8.5 minutes. The remaining six rows return “Within range.”
Lucas is included because his z-score is exactly 2.00. Change the comparison to >2 only if your rule should exclude the boundary.
Pro Tip: A two-standard-deviation cutoff is a rule chosen for this example. Set the threshold to match your own review policy and the way your data is interpreted.
Tips & Common Mistakes
- Use a positive standard deviation. STANDARDIZE returns #NUM! when
standard_devis zero or negative. - Choose STDEV.P or STDEV.S deliberately. STDEV.P describes the complete population. STDEV.S estimates standard deviation from a sample.
- Do not confuse a z-score with a percentile. STANDARDIZE reports distance from the mean in standard-deviation units. It does not return a probability.
- Keep units consistent. The value, mean, and standard deviation must describe the same measurement and use the same units.
- Use real numbers. Text values and error cells in the inputs can produce errors instead of z-scores.
- Normality affects later interpretation. STANDARDIZE can calculate a z-score for any numeric distribution, but probability statements based on that score may require distribution assumptions.
- Excel 2019 and earlier do not spill. Use a single-value formula in the first result cell and fill it down.
I use STANDARDIZE to express values on a common z-score scale, whether the supporting metrics are supplied or calculated from the data.
The examples also show how population and sample choices change results, and how a z-score can feed a practical review rule. I hope you found this article helpful.
