How to Calculate Z-Scores in Excel
Z-scores answer a fundamental question in data analysis: how unusual is this value? Raw numbers lack context. Telling someone a test score is 78 means nothing without knowing the average and spread...
Key Insights
- Z-scores transform raw data into standardized units, letting you compare values across different scales and identify outliers with statistical precision
- Excel offers two approaches: manual calculation with AVERAGE() and STDEV functions, or the cleaner STANDARDIZE() function—both produce identical results
- Choosing between STDEV.P() (population) and STDEV.S() (sample) matters more than most analysts realize; using the wrong one inflates or deflates your z-scores
Introduction to Z-Scores
Z-scores answer a fundamental question in data analysis: how unusual is this value? Raw numbers lack context. Telling someone a test score is 78 means nothing without knowing the average and spread of all scores. A z-score of +2.1 immediately communicates that the value sits 2.1 standard deviations above the mean—a genuinely exceptional result.
In practical terms, z-scores serve three primary purposes. First, they standardize data, converting any distribution to a common scale with a mean of 0 and standard deviation of 1. Second, they enable cross-distribution comparisons—you can meaningfully compare a student’s math score against their verbal score even when the tests use different scales. Third, they provide a statistical basis for identifying outliers, flagging values that fall unusually far from the center.
Financial analysts use z-scores to detect anomalous transactions. Quality control engineers use them to identify manufacturing defects. Researchers use them to normalize experimental data. If you work with numbers, you’ll eventually need z-scores.
The Z-Score Formula
The z-score formula is straightforward:
z = (x - μ) / σ
Breaking down each component:
- x = the individual value you’re analyzing
- μ (mu) = the mean of your dataset
- σ (sigma) = the standard deviation of your dataset
The numerator (x - μ) measures how far your value deviates from the mean. The denominator (σ) scales that deviation by the dataset’s natural spread. The result tells you how many standard deviations separate your value from average.
Consider a concrete example. Your dataset has a mean of 50 and standard deviation of 10. A value of 65 produces:
z = (65 - 50) / 10 = 1.5
This value sits 1.5 standard deviations above the mean. A value of 35 produces z = -1.5, sitting equally far below. The sign indicates direction; the magnitude indicates distance.
Method 1: Manual Calculation with Basic Functions
The manual approach uses Excel’s fundamental statistical functions. This method offers transparency—you see each calculation step—and flexibility for complex scenarios.
Assume your data occupies cells A2 through A100. In cell B2, enter:
=(A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100)
The dollar signs create absolute references, locking the range when you copy the formula down. Without them, Excel shifts the range as you drag, breaking your calculation.
Here’s a complete setup for a small dataset:
| A (Value) | B (Z-Score Formula) |
|-----------|--------------------------------------------------|
| 45 | =(A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 52 | =(A3-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 48 | =(A4-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 61 | =(A5-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 39 | =(A6-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 55 | =(A7-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 42 | =(A8-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 58 | =(A9-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 50 | =(A10-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
| 47 | =(A11-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11) |
For cleaner spreadsheets, calculate the mean and standard deviation once in separate cells:
| Cell | Formula | Purpose |
|------|----------------------------|----------------------|
| D1 | =AVERAGE($A$2:$A$100) | Dataset mean |
| D2 | =STDEV.S($A$2:$A$100) | Dataset std dev |
| B2 | =(A2-$D$1)/$D$2 | Z-score calculation |
This approach improves performance on large datasets since Excel calculates the mean and standard deviation once rather than recalculating for every row.
Method 2: Using the STANDARDIZE Function
Excel’s STANDARDIZE function wraps the z-score calculation into a single call. The syntax:
=STANDARDIZE(x, mean, standard_dev)
For our dataset in A2:A100, the formula in B2 becomes:
=STANDARDIZE(A2, AVERAGE($A$2:$A$100), STDEV.S($A$2:$A$100))
The function produces identical results to the manual method. Its advantage lies in readability—anyone scanning your spreadsheet immediately understands the intent.
You can also combine STANDARDIZE with pre-calculated statistics:
| Cell | Formula |
|------|--------------------------------------|
| D1 | =AVERAGE($A$2:$A$100) |
| D2 | =STDEV.S($A$2:$A$100) |
| B2 | =STANDARDIZE(A2, $D$1, $D$2) |
One caveat: STANDARDIZE requires you to supply the mean and standard deviation as arguments. It won’t calculate them automatically. This design choice actually benefits you—it forces explicit decisions about which statistics to use and prevents hidden assumptions.
Population vs. Sample Standard Deviation
This distinction trips up many analysts. Excel offers two standard deviation functions:
- STDEV.P() — population standard deviation, divides by N
- STDEV.S() — sample standard deviation, divides by N-1
The difference matters. STDEV.S applies Bessel’s correction, adjusting for the bias introduced when estimating population variance from a sample. The correction increases the standard deviation slightly, which decreases your z-scores slightly.
Here’s a side-by-side comparison:
| Metric | Formula | Result |
|-----------------------|------------------------------|---------|
| Sample Size | =COUNT(A2:A11) | 10 |
| Mean | =AVERAGE(A2:A11) | 49.7 |
| Population Std Dev | =STDEV.P(A2:A11) | 6.49 |
| Sample Std Dev | =STDEV.S(A2:A11) | 6.84 |
| Z-Score (Pop) for 61 | =(61-49.7)/6.49 | 1.74 |
| Z-Score (Sample) for 61| =(61-49.7)/6.84 | 1.65 |
The difference of 0.09 in z-scores might seem trivial, but it compounds across analyses. With smaller samples, the gap widens considerably.
When to use which:
Use STDEV.P when your data represents the entire population—every possible observation. This applies when analyzing all employees in a small company, all products in a catalog, or all students in a specific class.
Use STDEV.S when your data represents a sample drawn from a larger population. This covers most real-world scenarios: survey responses, experimental measurements, historical data used to make predictions.
When uncertain, default to STDEV.S. The sample correction provides a more conservative estimate, reducing the risk of overstating statistical significance.
Practical Application: Identifying Outliers
Z-scores provide a principled method for outlier detection. The standard thresholds:
- |z| > 2 — unusual values (roughly 5% of normally distributed data)
- |z| > 3 — extreme outliers (roughly 0.3% of normally distributed data)
Here’s a complete outlier detection setup:
| A (Value) | B (Z-Score) | C (Status) |
|-----------|--------------------------------------------------|-------------------------------------|
| 45 | =STANDARDIZE(A2,$E$1,$E$2) | =IF(ABS(B2)>2,"Outlier","Normal") |
| 52 | =STANDARDIZE(A3,$E$1,$E$2) | =IF(ABS(B3)>2,"Outlier","Normal") |
| 89 | =STANDARDIZE(A4,$E$1,$E$2) | =IF(ABS(B4)>2,"Outlier","Normal") |
| 48 | =STANDARDIZE(A5,$E$1,$E$2) | =IF(ABS(B5)>2,"Outlier","Normal") |
For more nuanced classification, use nested IF statements:
=IF(ABS(B2)>3, "Extreme Outlier", IF(ABS(B2)>2, "Mild Outlier", "Normal"))
Add conditional formatting to make outliers visually obvious. Select your z-score column, create a new rule using a formula, and apply:
=ABS(B2)>2
Set the format to a bold red fill. Your outliers now jump off the screen.
For automated outlier counting:
| Metric | Formula |
|---------------------|--------------------------------------|
| Count |z| > 2 | =COUNTIF(B2:B100,">2")+COUNTIF(B2:B100,"<-2") |
| Count |z| > 3 | =COUNTIF(B2:B100,">3")+COUNTIF(B2:B100,"<-3") |
| Percentage Outliers | =D1/COUNT(B2:B100) |
A cleaner approach using SUMPRODUCT:
=SUMPRODUCT((ABS(B2:B100)>2)*1)
This counts all cells where the absolute z-score exceeds 2 in a single formula.
Conclusion
Z-score calculation in Excel comes down to two reliable methods: manual formulas with AVERAGE and STDEV functions, or the STANDARDIZE function for cleaner syntax. Both produce identical results when configured correctly.
The critical decision isn’t which method to use—it’s whether to apply population or sample standard deviation. Default to STDEV.S unless you’re certain your data encompasses the entire population of interest.
Z-scores assume your underlying data follows a roughly normal distribution. For heavily skewed data, z-scores still function mathematically but lose their probabilistic interpretation. Consider transforming skewed data (log, square root) before calculating z-scores, or use non-parametric alternatives like percentile ranks.
From here, z-scores open doors to more advanced analyses. Use them with NORM.S.DIST to calculate exact probabilities. Apply them in hypothesis testing to determine statistical significance. Combine them across variables to create composite scores. The standardized scale you’ve created becomes the foundation for increasingly sophisticated statistical work.