What a z-score does and why you might calculate one

A z-score tells you how far a single data point sits from the average of your dataset, measured in standard deviations. If your dataset is test scores from a class, a z-score answers: "Is this student's score typical, unusually high, or unusually low compared to everyone else?" A z-score of 0 means the value equals the average. A z-score of 2 means the value is two standard deviations above average. A z-score of -1.5 means it is 1.5 standard deviations below average.

You calculate z-scores when you need to compare values that come from different scales or datasets. If you want to know whether a student's math score or their reading score was more impressive relative to their classmates, z-scores let you compare them directly. They are also the foundation for many statistical tests and for identifying outliers — data points that are unusually far from the rest.

Key Takeaways

  • The z-score formula is (value minus average) divided by standard deviation, and Excel has built-in functions to calculate each part.
  • You need three pieces: the individual data point, the average of all data points (using AVERAGE), and the standard deviation (using STDEV.S or STDEV.P).
  • A single Excel formula like =(A2-AVERAGE($A$2:$A$20))/STDEV.S($A$2:$A$20) calculates the z-score for the value in A2 against the entire dataset.
  • Dollar signs ($) in the formula lock the range so it does not change when you copy the formula down to other rows.
  • The STANDARDIZE function is a shortcut that does the same calculation in one step: =STANDARDIZE(A2, AVERAGE($A$2:$A$20), STDEV.S($A$2:$A$20)).

The three components you need before you start

Before you write any formula, gather your data in a single column. Let's say your values are in cells A2 through A20 (with a header in A1). You will need to know or calculate three things: the individual value you are measuring, the mean (average) of the entire dataset, and the standard deviation of the entire dataset.

Excel has functions for the last two. The AVERAGE function calculates the mean. The STDEV.S function calculates the standard deviation for a sample (use this most of the time). The STDEV.P function calculates the standard deviation for an entire population (use only if your data represents every single member of a group, not a sample from a larger group). For most real-world work, STDEV.S is the right choice.

Building the formula step by step

The z-score formula is: (individual value minus the average) divided by the standard deviation. In Excel, if your data is in column A from row 2 to row 20, and you want the z-score for the value in A2, the formula is:

=(A2-AVERAGE($A$2:$A$20))/STDEV.S($A$2:$A$20)

The dollar signs ($) are critical. They lock the range so that when you copy the formula down to calculate z-scores for A3, A4, and so on, the AVERAGE and STDEV ranges stay the same. Without them, the range would shift with each row and give you wrong answers. The A2 at the beginning has no dollar signs because you want it to change to A3, A4, A5 as you copy down.

Type this formula into cell B2 (or any empty column next to your data). Press Enter. Excel calculates the z-score for that row. Then click on B2 again, copy the cell, select the range B3:B20, and paste. The formula copies down and calculates z-scores for every row.

Using the STANDARDIZE function as a shortcut

Excel has a built-in function called STANDARDIZE that does the same calculation in one step. The syntax is =STANDARDIZE(value, average, standard_deviation). For the same dataset, the formula would be:

=STANDARDIZE(A2, AVERAGE($A$2:$A$20), STDEV.S($A$2:$A$20))

This produces the exact same result as the manual formula. It is slightly shorter to type and reads more clearly if you are sharing the spreadsheet with someone else. Copy this formula down the same way: paste it into B2, then copy and paste to B3:B20.

A worked example with real numbers

Suppose you have five test scores: 72, 85, 78, 91, and 88. You want to find the z-score for the score of 85. First, calculate the average: (72 + 85 + 78 + 91 + 88) / 5 = 82.8. Next, calculate the standard deviation. Using the sample formula (STDEV.S), the standard deviation is approximately 7.56. Now explore the z-score formula: (85 - 82.8) / 7.56 = 0.29. This means the score of 85 is about 0.29 standard deviations above the average — slightly above typical, but not unusual.

In Excel, put the five scores in A2:A6. In B2, type =STANDARDIZE(A2, AVERAGE($A$2:$A$6), STDEV.S($A$2:$A$6)). Press Enter. The result is 0.29 (rounded). Copy this formula to B3:B6 to see z-scores for all five values. You will see that 91 has the highest z-score (about 1.08) because it is furthest above the average.

Interpreting z-scores once you have them

A z-score of 0 means the value equals the average. Positive z-scores are above average; negative z-scores are below average. The further from 0, the more unusual the value. In a typical dataset, about 68% of values fall between -1 and 1, about 95% fall between -2 and 2, and about 99.7% fall between -3 and 3. If you see a z-score of 3 or higher (or -3 or lower), that data point is an outlier worth investigating.

Z-scores are also useful for comparing across different datasets. If one student scored 92 on a math test where the class average was 85, and another student scored 88 on a reading test where the class average was 80, their raw scores are close — but their z-scores tell you which performance was more impressive relative to their peers. Calculate both z-scores and compare.

Common mistakes to watch for

The most frequent error is forgetting the dollar signs in the AVERAGE and STDEV ranges. Without them, the ranges shift as you copy the formula down, and every z-score will be wrong. Always use $A$2:$A$20 (or whatever your range is) for the average and standard deviation, and leave the individual value without dollar signs so it updates with each row.

Another mistake is using STDEV.P when you should use STDEV.S. STDEV.P is for a complete population; STDEV.S is for a sample. Unless you are certain your data represents every member of a group, use STDEV.S. Using the wrong function will give you a slightly different (and incorrect) z-score.

A third pitfall is including the header row in your range. If your header is in A1 and your data starts in A2, make sure your AVERAGE and STDEV ranges are $A$2:$A$20, not $A$1:$A$20. Excel will try to convert the header text to a number, which causes an error or incorrect result.

Frequently Asked Questions

What is the difference between STDEV.S and STDEV.P?

STDEV.S calculates standard deviation for a sample — a subset of a larger group. STDEV.P calculates it for a population — the entire group. Use STDEV.S unless you are certain your data includes every single member of the group you are studying. Most real-world datasets are samples, so STDEV.S is the default choice.

Can I calculate z-scores for text or categorical data?

No. Z-scores only work with numerical data. You cannot calculate a z-score for categories like "red," "blue," or "green." You can calculate z-scores for numbers like ages, prices, test scores, or measurements.

What does a negative z-score mean?

A negative z-score means the value is below the average. For example, a z-score of -1.5 means the value is 1.5 standard deviations below the average. The further negative the z-score, the further below average the value is.

How do I find outliers using z-scores?

Values with z-scores greater than 3 or less than -3 are typically considered outliers. Some analysts use a threshold of 2 or 2.5 depending on how strict they want to be. Calculate z-scores for all your data, then sort or filter to find any values outside your chosen threshold.

Can I use z-scores to compare datasets with different units?

Yes. That is one of the main reasons to use z-scores. If one dataset is in dollars and another is in hours, their raw values are not comparable. But z-scores are unitless — they measure distance from the average in standard deviations — so you can compare them directly across different scales.