In this tutorial, I will show you how to calculate Z scores by using Microsoft Excel.
What is a Z score?
A Z score is the number of standard deviations (SDs) away from the mean. Calculating Z scores is a way of standardizing values in a data set.
The formula to calculate a Z score can be found below.
To calculate a Z score, there are two pieces of information we need about the data set…
- Mean (μ) – The mean, or average, value of the data set
- SD (σ) – The SD of the data set
The Z score is then calculated by subtracting the mean away from the data point and then dividing this answer by the SD.
How to calculate Z scores in Excel
There are two ways to calculate Z scores in Excel. The first option is to manually enter the Z scores formula, while the second option uses the STANDARDIZE formula in Excel.
Let’s start with the manual approach first.
1. Calculate the mean of the data set
For this tutorial, I have a simple data set of test scores from 10 students.
The first step is to calculate the mean (average) of the data set. This can easily be done by using the AVERAGE formula in Excel.
In a new cell, enter the following formula.
=AVERAGE(number1)
Replace number1 with the range of cells containing the data.
The mean for my data set is 76.9.
2. Calculate the SD of the data set
The next step is to calculate the SD of the data set.
To do this, we can use the STDEV formula in Excel. This formula is identical to the STDEV.S formula since it calculates the SD of the sample.
Note, if you want to calculate the SD of the population, then use the STDEV.P formula instead.
In a new cell, enter the following formula.
=STDEV(number1)
Again, replace number1 with the range of cells containing the data.
The SD of my data set is 13.478.
3. Calculate the Z score of each raw value
Now we are ready to manually calculate the Z score in Excel.
In an empty cell, preferably next to the raw data value, enter the following formula.
=(Raw data value - Mean)/SD
Replace the following in the equation with…
- Raw data value – This is the cell containing original data value that you want to convert to a Z score
- Mean – The cell containing the mean value of the data set
- SD – The cell containing the SD of the data set
The Z score for my first data value (72) was -0.36.
This means that this data value is 0.36 SDs below the mean value (76.9). You can tell that it is below the mean since the Z score is a negative value. If it was a positive value, then this would mean it is above the mean.
Now you just need to copy the formula down so that you create Z scores for all of the data points.
Before you do this, make sure you lock the cells in the formula containing the mean and SD cells. This is to ensure that these cells in the formula do not change when you drag the formula down.
To lock the cells in Excel, enter a $ symbol before the column letter and the row number for that cell. Alternatively, a shortcut is to select the cell in the formula and press F4.
Below is a screenshot of what the formula now looks like with the mean and SD cells locked.
Now you can click and drag the formula down to autocomplete the Z score calculations for the rest of the data set.
Using the STANDARDIZE formula
An alternative was to calculate Z scores in Excel is to use the STANDARDIZE formula.
To be able to use this formula, you need to calculate the mean and SD as before. Then, use the following formula to calculate the Z score.
=STANDARDIZE(x, mean, standard_dev)
Replace the following components of the formula with…
- x – The cell containing the raw data value
- mean – The cell containing the mean value of the data set
- standard_dev – The cell containing the SD of the data set
Remember to lock the mean and SD cells again before you apply this formula down.
Conclusion
In this tutorial, I have shown you two ways to calculate Z scores in Excel. To calculate a Z score, you need information about the mean and SD of the data set. Then, you can either manually enter the formula yourself or use Excel’s STANDARDIZE formula to calculate the Z score.
Microsoft Excel version used: 365 ProPlus
