Calculating the Z-score Using Excel

Cell Preparation

Here’s an image of the cells prepared for calculating Z scores.

I hypothesized that we want to calculate the Z scores of a variable X that has the following scores: 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10.

• First, vertically list the numbers of the variable (X) based on which you want to calculate the Z score. At the bottom of the dataset, prepare a cell to show the sum (∑) of the scores.
  • You don’t have to use the ∑ notation. You can simply have SUM instead of ∑ .
    • If you want to insert the ∑ notation, then, click the Insert option from the main menu ribbon, and at the right end, click the Symbol option (see below).
      • Then, you can look up the ∑ symbol or use the character code of 2211 to find it faster.

Finally, to insert the sum, you would want to use the AutoSum function. Specifically, select the cell next to the Sum notation (∑), which is B12 cell, and click the AutoSum function as shown below:

If you hit enter, then, the sum of 55 will be automatically inserted.

Then, next to the variable name, create three columns for the following:

1. Deviations: (X – M_x)
2. Squared deviations: (X – M_x)²
3. Z-score: (X – M_x)²/S_x

Working on these three cells will be the main process of getting the Z scores.

As a final process of the cell preparation, at the bottom of the table, let’s make four cells for the following:

N: sample size

M_x = Mean of the variable

S_x² = Variance

S_x= Standard Deviation

Let’s fill in the N and M_x. N refers to the sample size of the variable. In other words, how many scores are there in the variable. In our case, N = 10.

M_x is the mean of the variable, which you can calculate using the AutoAverage function. After you select the cell next to the M_x (Cell B15), click drop-down arrow of the the AutoSum function, and choose, Average:

After selecting the Average, you need to define the input cells as shown below (B2:B11) by manually selecting the cells or typing in the range of (B2:B11).

Then, the average of the variable, 5.5, will be inserted.

Z-score Calculation Using Excel

1. Deviation calculation: (X – M_x)

• As the formula suggests, the deviation refers to the difference between individual X score (e.g., 1) and the mean (M_x), which can be calculated by grabbing each score of X and subtract the mean. For example, for the first deviation score, select the first cell of the deviation column and input the the following command: =B2-15
  • *Make sure to lock the cell for the average (B15) by putting the dollar symbol before the column label and the row label. so that the same average is used across the remaining cells (see below).

To replicate the same deviation-calculation process across the remaining cells, choose the first deviation score (cell C2) and move the cursor at the lower right corner of the cell.

Then, the cursor will be a fat crosshair. Then, left-click and drag the cursor until row 11, which has the last score of the variable.

2. Squared deviation calculation: (X – M_x)²

One can square a cell value in Excel using =(the cell to be squared)^2. For example, to square the first deviation score, we can use this command in Cell D: =C2^2

Then, as we did automatic fill-in in column B, we can do a similar automatic fill-in in column C. Specifically, choose the first squared deviation score (cell D2) and move the cursor at the lower right corner of the cell. Then, the cursor will be a fat crosshair. Then, left-click and drag the cursor until row 11, which has the last score of the variable.

Finally, at the bottom of the squared deviation column (Cell D12), calculate the sum of the squared deviation scores using the AutoSum (in this example, 82.5).

3. Variance (S_x²) calculation.

The formula of variance can be verbally defined as the sum of squared deviations divided by N-1.

Therefore, if we divide the sum of squared deviations (82.5 in Cell D12) by N-1, which is 9, then, we get the variance of 9.167.

4. Standard deviation (S_x) calculation.

By square rooting the variance, we can get the standard deviation, which is 3.028 as shown below: (note the command for square root in Excel is sqrt)

5. Z score calculation

Here’s the formula for the Z-score:

   

As you may have noticed, we have prepared all the ingredients of the Z formula. That is, we have the numerator (Deviation) and the denominator (standard deviation, s). Specifically, column C shows each individual score’s deviation score and cell D15 shows the standard deviation. Therefore, to calculate the Z-score, we just need to divide each deviation score by the standard deviation.

Let’s work on the first X score’s Z-score (see below)

If you hit enter, then, you would get the Z-score of -1.486 for the first X score.

Make sure to lock the standard deviation (15) so that when we attempt to automatically fill-in the remaining cells in column E, the locked standard deviation value is used. Specifically, as we did earlier to do automatic fill-in, we move the cursor at the lower right corner of the first Z-score cell, and drag down the fat crosshair. Then, we will get the Z-scores for all the remaining cells: