Z Score in Excel (Using Functions & Formulas Tab)

Get FREE Advanced Excel Exercises with Solutions!

Z score is a measurement that indicates the relationship of a datapoint to the mean of the dataset. It calculates the number of standard deviations by which data points differ from the mean. Z score is used to determine the relative positions of data points in a dataset. It is useful to calculate probability, outliers, etc. that help in taking calculated risks and making important decisions.

In this article, we will explore how to calculate the Z score in Excel. We will explain three methods to find the Z score which are using functions, using the Formulas tab, and without helper columns.

Calculate Z Score Using Formulas Tab


Download Practice Workbook

Download this practice workbook while reading this article.


How to Find Z Score in Excel

1. Using AVERAGE and STDEVPA Functions

  • Select cell G4 and write the following formula using the AVERAGE function to determine the average.
=AVERAGE(C5:C12)

Find Average

  • Then calculate the standard deviation by applying the following formula with the STDEVPA function in cell G5.
=STDEVPA(C5:C12)

Find Standard Deviation

  • Then use the formula below to find the Z score of the value in cell C5.
=(C5-$G$4)/$G$5
  • Finally, Autofill the rest of the cells to get the Z score for all data.

Find Z Score Using AVERAGE and STDEV Functions


2. Using Formulas Tab

  • Select cell G4 and go to,

Formulas → More Functions → Statistical → AVERAGE

Use Average Function to Calculate Z Score Using Formulas Tab

  • The Function Arguments dialogue box will appear. Insert the range C5:C12 and click OK to get the average cell G4.

Insert Average Function Arguments

  • Similarly, select cell G5 and go to, Formulas More Functions → Statistical → STDEVPA.

Apply STDEVPA Function Using Formulas Tab

  • In the Function Arguments box, select the range C5:C12 in Value1 and press OK to determine the standard deviation.

Insert Function Arguments to Determine Standard Deviation

  • Now select cell D5 and navigate to,

Formulas → More Functions → Statistical → STANDARDIZE

Insert Standardize Function Using Formulas Tab

  • In the Function Arguments dialogue, select cell C5 as the value of X.
  • Then insert $G$4 as the Mean and $G$5 as the Standard_dev.
  • Click on OK to get the Z score.

Apply Function Arguments to Calculate Z Score

  • Finally, Autofill D6 to D12 to get Z scores for all the values.

Calculate Z Score Using Formulas Tab


3. Without Helper Cells

In this example, we will use the STDEV.S and STEV.P functions to calculate the Z score.

  • Select cell D5 and insert the following formula to find the Z score using the S function.
=(C5-AVERAGE($C$5:$C$12))/STDEV.S($C$5:$C$12)

Insert Formula to Calculate Z Score without Helper Column

  • Similarly, click on cell E5 and type the formula given below to determine the Z score using the STDEV.P function.
=(C5-AVERAGE($C$5:$C$12))/STDEV.P($C$5:$C$12)

Calculate Z Score without Helper Column


Frequently Asked Questions (FAQs)

1. Can we do Z test in Excel?

Z test in Excel can easily be done using the ZTEST function. The ZTEST function takes an array of data and an X value as arguments. It returns the one-tailed probability associated with the Z test.

2. What does z-score tell us?

The Z score of a data point tells us how many standard deviations the value is away from the mean of the whole dataset. It is useful to compare various data points in a dataset.

3. Is there a ZSCORE function in Excel?

Unfortunately, there is no ZSCORE function in Excel. Although it can be created using the VBA code. Besides, Z score can easily be calculated using the AVERAGE and STDEVPA functions as explained above.


Things to Remember

  • If you are working with the entire dataset of the population, use the P function.
  • If you are working with a sample of a dataset, use the S function.
  • Verify if your data is normally distributed as the Z score assumes that data follows a normal distribution.

Conclusion

Thanks for making it this far. I hope you found this article useful. In this article, we have described three methods on how to calculate Z score in Excel. We have used the AVERAGE and STDEV functions, the Formulas tab to find the Z score. We have also determined the Z score without helper columns.  If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.


Z Score in Excel: Knowledge Hub


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo