Let's say we've collected data on the number of hours that each member of a sample of college
students sleep each night. In Figure 1 below, these data are illustrated in Column A with the
Column Heading "Sleep (in hours).” !
First, we need to calculate the Mean (illustrated in cell D2 in Figure 1 below) and the Standard
Deviation (illustrated in cell E2 below) because we’ll need the Mean and Standard Deviation to
calculate the z-score of each value in our data set.!
Then, to calculate each z-score, we first create a new Column Header called Z-Scores, as
illustrated in Column B of Figure 1 below. Next we build a formula.!
The formula for computing a z-score is =(DataValue-Mean)/StDev. For example, to compute a z-
score for the first value in our data set, we use the formula =(A2-$D$2)/$E$2 as Figure 1 illustrates. !
Notice the dollar signs $ in the formula. These dollar signs are what are known as absolute
markers. They tell the formula to always look in a specific cell for a value, even if the formula is
copied into another cell. For example, with the formula =(A2-$D$2)/$E$2, our formula will always
look in cell D2 for the Mean and always look in cell E2 for the Standard Deviation. !
When we use the dollars signs, we need to place a dollar sign around both the letter and the
number of a cell name. For example, we need to place a dollar sign around both the letter D and
the number 2 (e.g., $D$2) to tell the formula to always look in cell D2 for the Mean.!
Once we’ve calculated the z-score for the first data value in our data set, we can calculate the z-
scores for all the other values. Because we used the dollar signs in our formula to tell our formula
to always look in a specific cell for the Mean and to always look in another specific cell for the
Standard Deviation, we can take advantage of a short cut to compute all the other z-values.!
How To Calculate z-scores in
Microsoft Excel, Google Sheets, and Apple Numbers
Chelsea Andrews, M.S.
Figure 1 - Excel and Google Sheets
Figure 1 - Apple Numbers
