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.
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.
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.
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.
Step 2) Calculate the standard deviation for this dataset using the STDEV.S function.
Step 3) Subtract the mean for this dataset from each value of the dataset. We will call this value x.
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 SpreadsheetoStep 4) The final step is to calculate the Z-score. Divide X by the Standard Deviation of this dataset through the following formula.
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.
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.
Step 3) For the mean argument, nest in the AVERAGE function as follows.
Step 4) For the standard_dev argument, nest in the STDEV.S function as follows.
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.
Step 6) Drag this formula down the list of weights.
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 SpreadsheetoConclusion
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.
Written by Kasper Langmann
My name is Kasper Langmann, and I’m the co-founder of Spreadsheeto and a certified Microsoft Office Specialist.
This tutorial reflects over 14 hours of dedicated research and writing, based on my 10+ years of professional Microsoft Excel experience.
Last updated on April 3rd, 2024.
One last thing before you go: make sure to sign up for my free Microsoft Excel course!
p>