How to Calculate Z Score in Excel (2 Easy Methods)

In Statistics, the term Z-score is very popular. The Z-score denotes how far the data value is from the mean. As an Excel user, you will be glad to know that the Z-score can be calculated in Excel. To calculate the Z-score in Excel, you need to have data value, average value, and standard deviation value. This article will mainly focus on how to calculate Z-score in Excel. I hope you find this article very informative and gather a lot of knowledge regarding Z-score.

What Is Z Score?

The Z-score can be defined as the position of a certain score from the mean value of the dataset. The score can be positive when it is above the mean value whereas it can be negative when it is below the mean value. The Z-score is measured in standard deviation units.

Here,

Z = It demonstrates the Z-score

X = It denotes the value that you want to standardize

µ = It denotes the mean or average of the given dataset

σ = It denotes the standard deviation of the given dataset

How to Calculate Z Score in Excel: 2 Easy Methods

To calculate Z-score in Excel, we have found two different methods including a conventional method. Another one is based on the STANDARDIZE function. Both of them are very effective to use and easy to digest. To show both of those methods, we take a dataset that includes some student name and their marks. From there, we want to find out the average value and standard deviation. After that, we shifted our focus to calculating the Z-score.

1. Calculate Z Score Using Conventional Formula

First of all, we want to show the conventional formula through which you can easily calculate the Z-score in Excel. To apply this method, you need to calculate the mean value of your dataset. After that, you need to calculate the standard deviation. By using those two values, you can easily calculate the required Z-score in Excel. Follow the steps carefully.

Step 1: Calculate Mean of Dataset

First, you need to calculate the mean value of the dataset. To do this you can use the AVERAGE function.

At first, select cell G4.

Then, write the following formula in the formula box:

=AVERAGE(C5:C12)

Press Enter to apply the formula.

Step 2: Calculate Standard Deviation of Dataset

After that, we need to calculate the standard deviation. The standard deviation can be defined as how scattered the data is in relation to the average value. We use the STDEVPA function to calculate the standard deviation perfectly.

First, select cell G5.

Then, write the following formula in the formula box.

=STDEVPA(C5:C12)

Press Enter to apply the formula.

Step 3: Calculate Z Score

Then, we need to focus on Z-score calculation. Primarily, we need to calculate the mean and standard deviation for Z-score. We did it in the above steps.

At first, select cell D5.

Next, write down the formula in the formula box.

=(C5-$G$4)/$G$5

Here, cell G4 and cell G5 denote the mean and standard deviation respectively. We make these cells absolute by using the ($) sign. That means it is fixed.

Press Enter to apply the formula.

Now, drag the Fill handle icon up to cell D12 to apply the formula down the column.

2. Use of STANDARDIZE Function to Calculate Z Score

We can calculate Z-score by using the STANDARDIZE function. We can get many more functions in our Excel Formulas command. This method is basically utilized in an effective way. Follow the steps carefully. We will also use the AVERAGE and STDEVPA functions to calculate mean and standard deviation respectively.

Step 1: Calculate Mean of Dataset

As we know before calculating Z-score, we need to calculate the mean of the given dataset.

At first, select cell G4.

Then, go to the Formulas tab in the ribbon.
From the Function Library, select More Functions.
Then, in the More Functions option, select Statistical.

In the Statistical section, there are lots of functions to use.
Then, select the AVERAGE function.

A Function Arguments dialog box will appear.
In the Number1 section, select the total range of cells.
Finally, click on OK.

As a result, it will provide the mean value of the dataset in cell G4.

Step 2: Calculate Standard Deviation of Dataset

Next, we need to calculate the standard deviation of the dataset.

First, select cell G5.

Then, go to the Formulas tab in the ribbon.
From the Function Library, select More Functions.
Then, in the More Functions option, select Statistical.

In the Statistical section, there are lots of functions to use.
Then, scroll down and select the STDEVPA function.

A Function Arguments dialog box will appear.
In the Value1 section, select the range of cells C5 to C12.
Finally, click on OK.

As a result, we get the following standard deviation. See the screenshot.

Step 3: Calculate Z Score

After that, we need to shift our focus to calculating the Z-score in Excel. As we have the mean value and standard deviation, it is quite easy to calculate the Z-score now.

First, select cell D5.

Then, go to the Formulas tab in the ribbon.
From the Function Library, select More Functions.
Then, in the More Functions option, select Statistical.

In the Statistical section, there are lots of functions to use.
Then, scroll down and select the STANDARDIZE function.

A Function Arguments dialog box will pop up.
Then, In the X section, select cell C5.

Then, in the Mean section, select cell G4 which is the required mean value of this dataset. As we want to use this value for other calculations, so, we need to make it an absolute cell reference.

In the Standard_dev section, select cell G5 which is the required standard deviation of this dataset. We also made this cell absolute by using the ($) sign.
Finally, click on OK.

As a result, we get the following Z-score. See the screenshot.

Then, drag the Fill handle icon down the column up to cell D12. See the screenshot.

Interpretation of Z Score in Excel

When we illustrate the Z-score, we need to identify how many standard deviations above or below the value are from the mean. The Z-score can be positive, negative, or zero. A positive Z-score can be defined as a specific value that is above the mean value whereas a negative Z-score can be defined as a specific value that is below the mean value. Finally, when the Z-score is zero it is equal to the mean value.

In our dataset, the mean value is 6 and the standard deviation is 11.25. If we consider the specific value e 306. So, the Z-score for this value is -0.23341 which means 306 is 0.23341 standard deviation below the mean or average value.
In another case, when the value is 310, we get the z-score is 12226. That means 310 is 0.1226 standard deviation above the mean value.

Download Practice Workbook

Download the practice workbook.

Calculate Z Score.xlsx

Conclusion

To calculate the Z-score in Excel, we have shown two different methods through which you can easily calculate the Z-score. We have also discussed why the mean value and the standard deviation are more important while calculating the Z-score. I hope you find this article very informative. If you have any questions, feel free to ask in the comment box below.

<< Go Back to Z Score in Excel | Statistical Significance in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!