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.

**Download Practice Workbook**

Download this practice workbook while reading this article.

**Table of Contents**Expand

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

- Calculate Z Score
- Calculate Critical Z Score
- Calculate Altman Z Score
- Calculate BMI Z Score
- Calculate Probability from Z-Score
- Calculate P Value From Z Score
- Find Outliers Using Z Score

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