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 to calculate Z-score in Excel.
If this is your first time using Excel, we recommend that you take five minutes to go through it Microsoft Excel Beginner’s Guide† You’ll learn how essential functions work, navigation shortcuts, creating workbooks, formatting data, and everything there is to know about using the spreadsheet program as a beginner.
Once you’re done with that, move on to the next section to learn how to calculate Z-score in Excel. But first, let’s briefly explain the Z-Score, its uses, why you might need to calculate one, and how to get it done.
What is a Z-score?
Z-score (also known as a “standard score”) is a statistic that emphasizes the relationship between values in a distribution. More precisely, it describes the position of values in a data set with respect to their mean and standard deviation. Z-Score allows for accurate measurement and comparison of values in a data set.
The mathematical formula for calculating the Z-score is (x-µ) /† where x = cell value, µ = mean and σ = standard deviation.
Companies sometimes use Z-Score to predict and estimate impending bankruptcy. In addition, it is an excellent measure to determine the financial position of an institution. Researchers also use the Z-Score to compare observations obtained 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 data set. Although you can extract the mean and standard deviation in any cell, in our worksheet we have created special columns for “Mean” and “Standard Deviation”. We also created a column for ‘Z-score’.
Our sample document contains the performance reviews of 10 employees in a paper company. Now let’s calculate the employee rating Z score.
Calculate average average
To calculate the mean mean of your data set, type =AVERAGE(select the first value in the dataset, press the column keyselect the last value within the data set range, press the close parenthesis key and press Enter† The formula should look like this:
You should see the mean or mean value of the data set in the cell where you entered the formula.
Calculate standard deviation
Excel also makes it quite easy to calculate the standard deviation of your data set with a few clicks.
Select a cell in the column “Standard deviation”, type =STDEV.P(and then select the first value in range, press the column keyselect the last value, enter the . in close parenthesisand press Enter† If in doubt, the resulting formula should be similar to the one below:
Calculate Z Score in Excel: Method 1
Excel has a STANDARDIZE function that returns the Z-score of a data set in a distribution. Select the first cell in the Z-score column and follow the steps below.
- Go to the formulas tab and select More formulas†
- Move your mouse over the Statistical option and select the NORMALIZE†
That will open a new Function Arguments window where you can 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 mean in the “Average” field and press the 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 previews the Z value. Press Okay continue.
To get the Z-score for other values, move the cursor to the lower right corner of the cell and drag the plus (+) icon the column down.
Excel copies the formula in the column and automatically generates the Z-score for other values in the corresponding rows.
Calculate Z Score in Excel: Method 2
As mentioned earlier, you can obtain the Z-score of a data point by subtracting the mean of the data set from the data point and dividing the result by the standard deviation. Using the (x-µ) / σ you can calculate the z-score in Excel by entering these values manually.
- Select the first cell in the Z-score column, type an equal sign (†) followed by a open parenthesisand select the first value in the column containing the datasets for which you want to calculate the Z-score. Then type a hyphenselect the arithmetic mean, press F4 to absolute/fix the mean and press the close parenthesis icon. Finally, press the forward slash ††) button, select the standard deviation and press F4 to lock the cell reference.
The final formula should look something like this: =(B2-$C$2)/$D$2† Press Enter to execute the formula.
Note that the formula only calculates the Z-score for the first value in the selected cell.
- Move your mouse to the bottom right corner of the first Z-score cell and drag the plus (+) icon the column down.
Your data set will most likely contain a mix of negative and positive Z-scores. A positive Z-score indicates that the value/score is higher than the mean mean of the data set. A negative Z-score, of course, tells the opposite: the value is below average. If a data point’s Z-score is zero (0), it’s because its value is equal to the arithmetic mean.
The larger a data point, the higher the Z-score. Go through your worksheet and you will realize that the small values have lower Z scores. Likewise, values smaller than the arithmetic mean will have negative Z-scores.
For example, in our sample worksheet, you’ll discover that “Michael” had the highest rating (78) and the highest Z-score (1.679659). In contrast, “Dwight” and “Kevin” both had the lowest ratings (34) and the lowest Z-score (-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 any questions or other helpful tips about calculating Z-score in Excel.