How to Calculate a Z-Score in Excel (Formula Guide)

The Z-score is a very important statistical measure that tells how far each value of a dataset lies from its mean.

Since standard deviation is the measure of the spread of a dataset, Z-score evaluates the distance of each value from the mean in terms of the standard deviation of the dataset.

You’d see it practically applied in corporate finance (to see which investments of a portfolio are performing above average or below average), in health, education, and so many more industries 🏦

Interested to see how this statistical metric works and how do you calculate in Microsoft Excel?

Cool – get your hands on the free practice workbook for this guide and join me in.

What is Z-score

The Z-score represents the relationship between the value of a dataset and the mean of the dataset. It is expressed in terms of the standard deviation of that dataset ⚡

Just like the mean denotes the central tendency of a dataset and standard deviation shows the spread of the dataset, the Z-score shows how far does each value of the dataset lies from its mean.

Formula

The formula of the Z-score breaks down the math that runs behind it.

Click to copy

So, if your dataset has 3 values say 10, 15, and 20.

The mean of this dataset is 15, and the standard deviation is ~ 4.08.

Basic Z-score

You see the difference between each value of the dataset and the mean of the dataset.

Instead of saying 10 is 5 less than 15 (the mean of this dataset), we say 10 is 1.225 standard deviations less from the mean 🏃‍♂️

How did I calculate 1.225? ( 5 / 4.08 )

-1.225 is the Z-score for 10.

How to calculate Z-score in Excel

You’ll find it super easy to calculate Z-score in Excel. Takes no more than some seconds and you have two methods to choose from.

Calculate it Manually

The first way to calculate the Z-score in Excel is to run the Z-score formula (as discussed above) in Excel.

To exemplify it to you, I have gathered the data of some people’s weights living in Town ABC.

Data of people and their weights

Let’s calculate the z-score for each one of them on the list to see how many standard deviations they are away from the mean of this dataset.

Step 1) is to calculate the mean for this dataset using the AVERAGE function.

Click to copy
Calculating the population mean value

Step 2) Calculate the standard deviation for this dataset using the STDEV.S function.

Click to copy
Calculating the standard deviation of the dataset

Pro Tip!

Excel offers two functions to calculate standard deviation:

  • STDEV.S: This function must be used to calculate the standard deviation if you’re working with a sample collected from a population.
  • STDEV.P: This function must be used when you’re dealing with the entire population and not with a sample drawn therefrom

In this example, I have used the STDEV.S function as the data drawn for weights is sample data and not the entire population.

Step 3) Subtract the mean for this dataset from each value of the dataset. We will call this value x.

Click to copy
Deducting mean from each value

Note that I have used an absolute reference for the mean (Cell G2). I don’t want Excel to change the cell reference G2 (the mean) when I drag the formula down across the column. Take care of that.

Kasper Langmann, co-founder of Spreadsheeto

Step 4) The final step is to calculate the Z-score. Divide X by the Standard Deviation of this dataset through the following formula.

Click to copy
standard score standard deviation

There you have your z-score values for this dataset calculated. It was all about a few easy steps 🤘

You’d see some z-scores as positive values and other as negative values.

This tells if the weight of a person is greater than the mean of the dataset (a positive z-score) or smaller than it (a negative z-score).

We will cover more details on how to interpret z-scores in the later sections.

Use the STANDARDIZE function

The second and relatively easier method to calculate z-scores in Excel is to use the STANDARDIZE function of Excel.

It is built into Excel and helps you calculate z-scores for any given dataset in a snap ⌛

Step 1) Begin writing the STANDARDIZE function as below.

Click to copy
STANDARDIZE function syntax

It has 3 arguments. By x, it means the values from the dataset. The other two are the mean and standard deviation of the dataset.

Step 2) Refer to the first value from the dataset as the first argument.

Click to copy
Referring first value

Step 3) For the mean argument, nest in the AVERAGE function as follows.

Click to copy
Mean of the dataset

Step 4) For the standard_dev argument, nest in the STDEV.S function as follows.

Click to copy
Standard deviation of the dataset

Make sure to use absolute references for the mean and the standard deviation argument so that the formula can be dragged down easily ⏬

Step 5) The function is ready. Give it the go-ahead by pressing Enter.

Z-scores of the dataset

Step 6) Drag this formula down the list of weights.

Z-scores of the dataset

The STANDARDIZE function calculates the z-score for each value of the dataset.

And the results are the same as that of our manual calculation. Both the methods for z-score calculation work absolutely fine – choose the one that you find better.

How to interpret Z-score

A z-score tells where your data stands in a data distribution. For example, if the Z-score for a data point is 1.5, this tells that the data point is 1.5 times away from the mean on the higher side 📝

The smaller or bigger the Z-score of a data point, the farther away it is from the mean of that dataset. It is not possible to objectively say if it is good or bad to have a smaller or bigger Z-score.

It will depend on what the data represents and how you want it to behave. To say if you’re an investor who is evaluating the return on his investments. A higher Z-score means your investment is performing better and offering higher returns than the average return on investments.

Hence, a bigger Z-score is not something bad in the said situation. It only tells how far or close each value of a data lies to its mean 🎯

In a standard normal distribution of data:

  • 65% of the data values will lie within -1 to 1 z-score.
  • 95% of the data values will lie between -2 to 2 z-score
  • And around 99% will lie between -3 to 3 z-score

Z-scores smaller or greater than -3 / +3 might require investigation (can call them outliers)

A Z-score of 0 means the value is the same as the mean of the dataset.

Kasper Langmann, co-founder of Spreadsheeto

Conclusion

Z-score is a very useful statistical measure that quickly tells you how far each value of your datum lies from its mean.

What makes it useful is the fact that it expresses this distance in terms of the data’s standard deviation. So, if a value lies absolutely one standard deviation away from the mean (at a standard spread), the Z-score for it would be a precise -1 or +1.

To learn more interesting statistical concepts, read out my following Excel tutorials, I’m sure you’d love them equally.