**How to Calculate Z-Score in Excel**:

As you know, there are many mathematical calculations that you can perform with Microsoft Excel. In this tutorial, we will walk you through the steps involved in calculating Z-score in Excel.

If you are using Excel for the first time, we recommend that you take five minutes to read this Microsoft Excel Beginner Tutorial. You’ll learn how essential functions work, navigation shortcuts, creating workbooks, formatting data, and everything there is to know about using a spreadsheet program as a beginner.

When you’re done with that, skip to the next section to learn how to calculate the Z score in Excel. But first, let’s take a brief look at the Z-score, its uses, why you might need to calculate one, and how to do it.

Table of Contents

## What is a Z-Score?

The Z-score (also known as the “standard score”) is a metric that highlights the relationship between values in a distribution. More precisely, it describes the position of values in a dataset with respect to their mean and standard deviation. Z-Score allows precise measurement and comparison of values across a dataset.

The mathematical formula for calculating the Z-score is **(x-μ) /**; where x = cell value, µ = mean, and = standard deviation.

Companies sometimes use Z-scores to anticipate and anticipate impending bankruptcy. Additionally, it is a great metric to gauge the financial position of an institution. Researchers also use Z-scores to compare observations from different samples or populations.

## How to Calculate Z-Score in Excel

Since the Z-score is a function of the mean and standard deviation, you must first calculate the mean and standard deviation of your dataset. Although you can work out the mean and standard deviation in any cell, we have created dedicated columns for “Mean” and “Standard Deviation” in our worksheet. We have also created a column for “Z-Score”.

Our sample document contains the performance rating of 10 employees in a paper company. Now let’s calculate the Z-Score of the rating of the employees.

**calculate mean average**

To calculate the mean average for your dataset, type **=average(**select , **first value** In the dataset, press **column key**select , **last price** Within the dataset range, press **closing bracket** key, and press **To enter**. The formula should look like below:

**=average(B2:B11)**

You should see the dataset’s mean or median value in the cell that you entered in the formula.

**Calculate Standard Deviation**

Excel also makes it very easy Calculate the standard deviation of your dataset In just a few clicks.

Select a cell in the “Standard Deviation” column, type **=STDEVP(**, then select **first value** In range, press **column key**, select the last value, enter **closing bracket**, and press **To enter**. In case you are in doubt, the resulting formula should be similar to the formula below:

**=STDEV.P(B2:B11)**

**Calculate Z-Score in Excel: Method 1**

Excel has a standard function that provides the z-score of a dataset in a distribution. Select the first cell in the Z-score column and follow the steps below.

- go to
**sources**tab and select**more threads**.

- hover your mouse over
**statistical**option and choose**testify**.

This will launch a new Function Arguments window where you will be able to calculate the Z-score of the distribution.

- Enter the cell reference of the first value in the “X” field.

- Enter the cell reference of the arithmetic average in the “Mean” field and press
**F4**on your keyboard to lock the cell reference.

- Finally, enter the cell reference of the standard deviation in the “Standard_dev” field and press
**F4**To lock the cell reference. The tool will display a preview of the Z-value. press**Ok**To move forward.

To get the Z-score for other values, move the cursor to the lower-right corner of the cell and drag **plus (+) icon** below the column.

Excel will copy the formula down the column and automatically generate z-scores for the other values in the respective rows.

**Calculate Z-Score in Excel: Method 2**

As mentioned earlier, you can get the z-score of a datapoint by subtracting the dataset’s mean from the datapoint and dividing the result by the standard deviation. Using (x-µ) / , you can calculate the z-score in Excel by inputting these values manually.

- Select the first cell in the Z-score column, type the equal sign (
**=**) after that**open bracket**, and select the first value in the column containing the datasets for which you want to calculate the Z-Score. Afterwards, type a**hyphen**, select the arithmetic mean, press**F4**To make the mean absolute/constant, press More**closing bracket**Mark. Finally, press**forward slash**(**/**) key, select the standard deviation, and press**F4**To lock the cell reference.

The final formula should look like this: **=(B2-$C$2)/$D$2**. press **To enter** to execute the formula.

Note that the formula will only calculate the Z-Score for the first value in the selected cell.

- Move and drag your mouse to the lower-right corner of the first Z-Score cell
**plus (+) icon**below the column.

## Interpreting the Z-score

Your dataset will most likely have a mix of negative and positive Z-Scores. A positive Z-score indicates that the value/score is higher than the mean of the dataset. A negative Z-score, of course, suggests the opposite: the value is below average. If a datapoint has a Z-score of zero (0), it is because its value is equal to the arithmetic mean.

The larger a data point, the higher its Z-Score. Look at your worksheet and you’ll find that smaller values tend to have lower Z-scores. Similarly, values smaller than the arithmetic mean will have negative Z-scores.

For example, in our sample worksheet, you’ll find that “Michael” had the highest rating (78) and the highest Z-score (1.679659). On the other hand “Dwight” and “Kevin” both had the lowest ratings (34) and lowest Z-scores (-1.59047).

## Become an Excel Expert: Tutorial Repository

You now know how to calculate the Z-score of a data set. Leave a comment below if you have questions or other useful tips regarding calculating Z-score in Excel.

We recommend reading tutorials related to Excel calculation of variance, remove duplicate rows in your dataset, and how Use Summary Functions in Excel To summarize the data.