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

## 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)`

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

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

### 2. Using Formulas Tab

• Select cell G4 and go to,

Formulas â†’ More Functions â†’ Statistical â†’ AVERAGE

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

• Similarly, select cell G5 and go to, Formulas â†’ More Functions â†’ Statistical â†’ STDEVPA.

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

• Now select cell D5 and navigate to,

Formulas â†’ More Functions â†’ Statistical â†’ STANDARDIZE

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

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

### 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)`
• 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)`

## 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 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

Advanced Excel Exercises with Solutions PDF