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.


Download Practice Workbook

Download the practice workbook.


What Is Z Score?

The Z-score can be defined as a 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 which you want to standardize

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

σ = It denotes the standard deviation of the given dataset


2 Easy Methods to Calculate Z Score in Excel

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 shift our focus to calculating the Z-score.

Calculate Z Score in Excel


1. Calculate Z Score Using Conventional Formula

First of all, we want to show the conventional formula through which you can easily calculate 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 also. 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

At 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)

Calculate Z Score in Excel

  • Press Enter to apply the formula.

Calculate Z Score in Excel

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)

Calculate Z Score in Excel

  • Press Enter to apply the formula.

Calculate Z Score in Excel

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.

Calculate Z Score in Excel

  • Press Enter to apply the formula.

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

Calculate Z Score in Excel

Read More: How to Calculate Critical Z Score in Excel (3 Suitable Examples)


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.

Calculate Z Score in Excel

  • 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.

Calculate Z Score in Excel

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

Calculate Z Score in Excel

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.

Calculate Z Score in Excel

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

Calculate Z Score in Excel

  • 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.

Calculate Z Score in Excel

Step 3: Calculate Z Score

After that, we need to shift our focus to calculating Z-score in Excel. As we have the mean value and standard deviation both, it is quite easy to calculate 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.

Calculate Z Score in Excel

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

Calculate Z Score in Excel

  • 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.

Calculate Z Score in Excel

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

Calculate Z Score in Excel

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

Calculate Z Score in Excel

Read More: How to Calculate P Value from Z Score in Excel (with Quick Steps)


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 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 when 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.

Conclusion

To calculate 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, and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo