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.

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

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

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