The fastest way to calculate a z-score in Excel

Excel has a built-in function called STANDARDIZE that calculates z-scores directly. A z-score tells you how many standard deviations a single data point sits away from the average of your dataset — useful when you need to compare values on different scales or spot outliers.

The formula is =STANDARDIZE(value, mean, standard_deviation). You supply three things: the individual number you're measuring, the average of your entire dataset, and how spread out that dataset is. Excel does the math and returns the z-score.

If you don't have the mean and standard deviation calculated yet, you'll need those first. Excel can find them for you with =AVERAGE() and =STDEV() or =STDEV.S() for a sample.

Key Takeaways

  • The STANDARDIZE function takes three inputs: the individual value, the mean of your data, and the standard deviation.
  • You can calculate the mean with =AVERAGE() and standard deviation with =STDEV.S() for a sample or =STDEV.P() for an entire population.
  • A z-score of 0 means the value equals the average; positive scores are above average, negative scores are below.
  • You can copy the STANDARDIZE formula down a column to calculate z-scores for every row in your dataset at once.

Setting up your data and calculating mean and standard deviation

Start by organizing your raw data in one column. Let's say your numbers are in cells A2 through A20. In an empty cell — say D2 — type =AVERAGE(A2:A20) and press Enter. This gives you the mean.

In another empty cell, type =STDEV.S(A2:A20) if your data is a sample, or =STDEV.P(A2:A20) if it's the entire population you're measuring. The difference matters: use .S when you're working with a subset, and .P when you have all the data. Press Enter.

Now you have three pieces: your raw data, the mean, and the standard deviation. You're ready to calculate z-scores.

Using STANDARDIZE to find individual z-scores

In a new column next to your data — say column B — click on cell B2. Type the formula =STANDARDIZE(A2,$D$2,$D$3), where A2 is your first data point, D2 holds your mean, and D3 holds your standard deviation.

The dollar signs ($) lock those cell references so they don't change when you copy the formula down. Without them, Excel would shift the references and give you wrong answers.

Press Enter. Excel calculates the z-score for that single value. If the result is 0, that value equals your average. A result of 2 means it's two standard deviations above average. A result of -1.5 means it's 1.5 standard deviations below.

Copying the formula to calculate z-scores for your entire dataset

Click on cell B2 (the one with your formula). Copy it with Ctrl+C (or Cmd+C on Mac). Select the range B3 through B20 — or however many rows you have — and paste with Ctrl+V.

Excel fills the column with z-scores for every value in your dataset. Each row now shows how far that individual number sits from the average, measured in standard deviations.

Check a few results by eye: values near your average should have z-scores close to 0. Your highest value should have the highest z-score. Your lowest value should have the lowest (most negative) z-score.

Understanding what your z-scores mean

A z-score of 0 means the value is exactly at the average. Positive z-scores are above average; negative z-scores are below. The larger the absolute value, the further from the center of your data.

In many fields, a z-score above 3 or below -3 is considered an outlier — a value so far from the average that it might be a measurement error or genuinely unusual. A z-score between -2 and 2 captures about 95 percent of your data if it follows a normal distribution.

Z-scores let you compare apples to apples even when the original numbers are on different scales. If one dataset has values from 0 to 100 and another from 0 to 10,000, their z-scores put them on the same footing.

Common mistakes and how to avoid them

The most frequent error is forgetting the dollar signs in the mean and standard deviation references. Without them, those cell addresses shift when you copy the formula down, and every z-score becomes wrong. Always use $D$2 and $D$3, not D2 and D3.

Another mistake is using the wrong standard deviation function. STDEV.S is for a sample (a subset of a larger population), and STDEV.P is for the entire population. If you're unsure, STDEV.S is usually correct for real-world data you've collected.

A third pitfall is calculating the mean and standard deviation from the wrong range. If your data is in A2:A20, make sure both AVERAGE and STDEV use that exact same range, or your z-scores will be meaningless.

Alternative: calculating z-scores manually if you prefer

If you want to see the math behind the function, you can build the formula yourself. The z-score formula is (value minus mean) divided by standard deviation. In Excel, that's =(A2-$D$2)/$D$3.

This gives the same result as STANDARDIZE but shows you exactly what's happening: you're subtracting the average from your value, then dividing by how spread out the data is. Both approaches work; STANDARDIZE is just shorter to type.

Frequently Asked Questions

What's 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 an entire population. If you're analyzing all the data you have, use STDEV.P. If your data is a sample from a bigger population, use STDEV.S. Most real-world datasets use STDEV.S.

Can I use STANDARDIZE with data that isn't normally distributed?

Yes. STANDARDIZE works on any dataset and tells you how many standard deviations a value is from the mean. However, the interpretation changes: z-scores are most useful for spotting outliers and comparing scales when your data follows a roughly normal (bell-curve) distribution.

What does a z-score of 2.5 actually mean?

It means that value is 2.5 standard deviations above the average. If your standard deviation is 10 and your average is 50, a z-score of 2.5 corresponds to the actual value 75. Z-scores let you compare that to other datasets without knowing their original scales.

Why do I get a #DIV/0! error?

This error means Excel is dividing by zero, which happens when your standard deviation cell is empty or contains 0. Check that you calculated STDEV correctly and that your data has variation (not all the same number). If all values are identical, standard deviation is zero and z-scores cannot be calculated.

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

No. Z-scores only work on numeric data — numbers you can average and measure spread on. For categories like "red," "blue," "green," you would use different statistical tools.