Calculate a Z-Score in Excel With STANDARDIZE
By ExcelReady TeamUpdated 5 min read
The short answer
A z-score shows how many standard deviations a value is above or below the mean. In Excel, use =(B2-AVERAGE($B$2:$B$8))/STDEV.P($B$2:$B$8), or =STANDARDIZE(B2,mean,standard_dev) with the mean and standard deviation in their own cells. Lock the ranges with $ so the formula fills down correctly.
Key takeaways
- Z-score = (value − mean) ÷ standard deviation. Positive is above average, negative is below.
- STANDARDIZE(x, mean, standard_dev) does the same sum, but you still calculate the mean and standard deviation yourself.
- Use STDEV.P when your data is the whole group, and STDEV.S when it's a sample.
- Lock the range with $ before you fill down, or every row compares against a different group.
- Values beyond about ±2 are unusual; many analysts check anything past ±3 as a possible outlier.
On this page+
A z-score in Excel tells you how far a value sits from the average, measured in standard deviations. You can calculate the z score with a plain formula, =(value-mean)/standard deviation, or with the STANDARDIZE function. This guide is for students, analysts and job-test takers who need to compare values on different scales or spot unusual ones.
What is a z-score?#
A z-score is a value's distance from the mean (the average), divided by the standard deviation. The standard deviation is a measure of how spread out the values are; the guide to calculating standard deviation in Excel explains it in detail.
z = (value − mean) ÷ standard deviation- 0 means the value is exactly average.
- Positive means above average. A z-score of 1.5 is one and a half standard deviations above.
- Negative means below average. A z-score of −1 is one standard deviation below.
All the examples use these test scores in A1:B8:
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Ana | 60 |
| 3 | Ben | 70 |
| 4 | Chen | 75 |
| 5 | Dara | 80 |
| 6 | Eli | 90 |
| 7 | Fatima | 65 |
| 8 | Gus | 85 |
The mean is 75 and, treating the class as the whole group, the standard deviation (STDEV.P) is 10.
How to calculate a z-score in Excel#
The direct way is one formula with AVERAGE and STDEV.P. In C2, type:
=(B2-AVERAGE($B$2:$B$8))/STDEV.P($B$2:$B$8)Press Enter. Ana's z-score is −1.5: her 60 is 15 points below the mean of 75, and 15 ÷ 10 = 1.5.
Two details make this work:
- The brackets. Excel divides before it subtracts, so without the brackets you'd get B2 − (AVERAGE ÷ STDEV.P), which is wrong.
- The
$signs. They lock the range so it stays B2:B8 when you fill down. Press F4 after selecting the range (Mac: ⌘T) to add them. The guide to absolute references and the dollar sign explains why.
Now fill C2 down to C8 (select C2:C8 and press Ctrl+D; Mac: ⌘D). The results:
| Student | Score | Z-score |
|---|---|---|
| Ana | 60 | −1.5 |
| Ben | 70 | −0.5 |
| Chen | 75 | 0 |
| Dara | 80 | 0.5 |
| Eli | 90 | 1.5 |
| Fatima | 65 | −1.0 |
| Gus | 85 | 1.0 |
The z-scores always average to 0, which is a quick check that your formula is right.
Calculate a z-score with STANDARDIZE#
STANDARDIZE does the same calculation in a named function:
=STANDARDIZE(x, mean, standard_dev)STANDARDIZE does not calculate the mean or standard deviation for you. Put them in their own cells first, which also makes the sheet easier to check:
- E2:
=AVERAGE(B2:B8)returns 75. The AVERAGE function guide covers how it treats blanks and text. - E3:
=STDEV.P(B2:B8)returns 10.
Then in C2:
=STANDARDIZE(B2,$E$2,$E$3)Ana's result is again −1.5. Fill down as before. STANDARDIZE works in every current version of Excel, on Windows and Mac. If the standard deviation is 0 or negative, it returns #NUM!.
Sample or population: which standard deviation?#
The choice of standard deviation changes the z-scores slightly. Excel has two:
- STDEV.P treats your data as the whole population: every student in this class, every order this month.
- STDEV.S treats your data as a sample from a bigger group, such as 50 customers out of thousands.
For our scores, STDEV.P gives 10 and STDEV.S gives about 10.80. With STDEV.S, Ana's z-score becomes about −1.39 instead of −1.5. Neither is wrong; they answer slightly different questions. The comparison of STDEV.S vs STDEV.P helps you choose. Whichever you pick, say which one you used.
How to read z-scores#
Z-scores tell you how unusual a value is. When data is roughly bell-shaped (a normal distribution), about 68% of values have a z-score between −1 and 1, and about 95% fall between −2 and 2. So:
- Between −1 and 1: typical.
- Beyond ±2: unusual.
- Beyond ±3: very unusual. Many analysts check these values as possible outliers or data-entry mistakes.
To find the most extreme value, =MAX(C2:C8) and =MIN(C2:C8) return 1.5 and −1.5 here. For cut-offs based on rank rather than distance, the guide to percentiles and quartiles is a better fit.
Compare scores from different tests#
Z-scores let you compare numbers on different scales. Say a student scored 80 in a maths test where the mean was 70 and the standard deviation 5, and 85 in an English test where the mean was 80 and the standard deviation 10.
- Maths:
=STANDARDIZE(80,70,5)returns 2. - English:
=STANDARDIZE(85,80,10)returns 0.5.
The English score is higher, but the maths result is far more impressive compared with the rest of the class.
Common z-score mistakes#
Most wrong z-scores come from one of these:
- Missing brackets.
=B2-AVERAGE(B2:B8)/STDEV.P(B2:B8)divides first. Put the subtraction in brackets. - Unlocked ranges. Without
$, the range slides down as you fill, and each row uses a different group. - Mixing up STDEV.S and STDEV.P between sheets, so results don't match a colleague's.
- Expecting STANDARDIZE to do everything. It needs the mean and standard deviation as inputs.
- Text in the data. AVERAGE and STDEV skip text, so a score stored as text quietly drops out of the mean.
Practise it#
A z-score is built from an average and a locked range. Practise AVERAGE, MIN and MAX in a live spreadsheet: you type the formula and it checks it instantly. Then master the $ in the relative vs absolute references lesson, one of the most-tested Excel skills, and try the z-score exercise on this page. The guide to AVERAGE, MIN and MAX is a good refresher too.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The class's standard deviation (STDEV.P) is in E2. Click C2 and work out Ana's z-score: her score in B2 minus the AVERAGE of B2:B8, divided by E2. Lock the ranges with $.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Z-score | Std dev | ||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 | ||||||
| 11 |
Tip: Put the subtraction in brackets first: (B2-AVERAGE($B$2:$B$8)). Then divide by $E$2.
Practise this in a lesson
Frequently asked questions
What is the formula for a z-score in Excel?
Use =(x-mean)/standard deviation, for example =(B2-AVERAGE($B$2:$B$8))/STDEV.P($B$2:$B$8). Or use =STANDARDIZE(B2,mean,standard_dev) with the mean and standard deviation in separate cells.
What does STANDARDIZE do in Excel?
STANDARDIZE returns a z-score. You give it the value, the mean and the standard deviation, and it returns (value minus mean) divided by the standard deviation. It does not work out the mean or standard deviation for you.
Should I use STDEV.S or STDEV.P for a z-score?
Use STDEV.P when the data is the whole group you care about, such as every student in a class. Use STDEV.S when the data is a sample from a bigger group. STDEV.S gives a slightly larger value, so z-scores come out slightly smaller.
What is a good z-score?
There is no good or bad z-score. Zero means exactly average, positive means above average and negative means below. A z-score past about plus or minus 2 means the value is unusual compared with the rest.
Why are all my z-scores zero or wrong after filling down?
The ranges probably moved as you filled. Lock them with dollar signs, such as $B$2:$B$8, so every row uses the same mean and standard deviation.