How to Calculate a Z-Score in Excel
Use Excel’s STANDARDIZE function or a direct formula to measure how many standard deviations a value is above or below the mean. The formula generator below locks your data range correctly.
Written and checked by TheToolNet Editorial Team · Last reviewed: September 12, 2026 · How we check spreadsheet guides
=STANDARDIZE(A2,AVERAGE($A$2:$A$21),STDEV.S($A$2:$A$21))Use STDEV.S for sample data or replace it with STDEV.P when the range is the entire population of interest.
Generate an Excel z-score formula
Enter the cell holding the score and the range containing the reference data. The generator returns STANDARDIZE, an equivalent direct formula, and the percentile formula.
Calculate z-scores in Excel step by step
- Place the observations in a column, such as A2:A21.
- In B2, enter
=STANDARDIZE(A2,AVERAGE($A$2:$A$21),STDEV.S($A$2:$A$21)). - Press Enter. A positive result is above the mean and a negative result is below it.
- Drag the fill handle down column B. The dollar signs keep the reference range fixed while A2 changes to A3, A4, and so on.
- Check that you used the correct standard deviation function for the study design.
STANDARDIZE versus the direct z-score formula
| Method | Excel formula |
|---|---|
| STANDARDIZE with sample SD | =STANDARDIZE(A2,AVERAGE($A$2:$A$21),STDEV.S($A$2:$A$21)) |
| Direct formula with sample SD | =(A2-AVERAGE($A$2:$A$21))/STDEV.S($A$2:$A$21) |
| STANDARDIZE with population SD | =STANDARDIZE(A2,AVERAGE($A$2:$A$21),STDEV.P($A$2:$A$21)) |
The two sample formulas are algebraically equivalent: both subtract the same mean and divide by the same sample standard deviation.
Convert an Excel z-score to a percentile
If B2 contains a z-score, enter =NORM.S.DIST(B2,TRUE) to return the cumulative proportion below it. Format the result as a percentage to display a percentile. For z = 1.96, the result is approximately 97.5%.
Use the z distribution calculator for left, right, two-tailed, and between-score probabilities, or check rounded values in the z-score table.
How to verify Excel z-scores
- Average check: the mean of all calculated z-scores should be extremely close to zero.
- Spread check: if you used the same SD convention throughout, the z-scores should have the expected standardized spread.
- Spot check: a value equal to the original mean must have z = 0.
- Formula check: inspect a filled-down cell and confirm the data range still contains dollar signs.
Common Excel z-score errors
| Problem | Cause and fix |
|---|---|
#NUM! | STANDARDIZE returns an error when the supplied standard deviation is zero or negative. |
| Changing range on fill-down | Use an absolute range such as $A$2:$A$21. |
| Different answer from another tool | Check whether one calculation uses STDEV.S and the other uses STDEV.P. |
| Text values ignored | Confirm the range contains numeric cells and that imported numbers are not stored as text. |
Frequently asked questions
Does Excel have a ZSCORE function?
Excel uses the STANDARDIZE function for this calculation. Supply the raw value, mean, and positive standard deviation.
Can I calculate one z-score from a known mean and SD?
Yes. Use =STANDARDIZE(score,mean,standard_deviation) with numeric values or cell references.
Should the mean and SD include the score being standardized?
Usually yes when the score belongs to the reference dataset. If an external population supplies the reference mean and SD, use those population parameters instead.