In this tutorial, you will learn how to calculate Z-Score in Excel.
Simply said, a Z-Score, also known as a standard score, informs you of how far a data point is from the mean. Technically speaking, however, it’s a measurement of how many standard deviations a raw score is from or above the population mean.
You can plot a Z-Score on a normal distribution curve. The range of Z-Scores ranges from -3 standard deviations (which would be on the far left of the normal distribution curve) to +3 standard deviations (which would fall to the far right of the normal distribution curve). You must be aware of the mean and population standard deviation in order to use a Z-Score.
Z-Scores allow results to be contrasted with those of a “normal” population. There are thousands of conceivable outcomes and unit combinations for test or survey findings, and those outcomes can appear meaningless. If you wish to compare someone’s weight to the “average” person’s weight, for instance, knowing that they weigh 100 kilograms may be useful information, but looking at a large table of data can be daunting (especially if some weights are recorded in kilograms). A Z-Score can show you how that person’s weight compares to the mean weight of the general population.
Once ready, we’ll get started by utilizing real-world examples to show you how to calculate Z-Score in Excel.
Table of Contents
Calculate Z-Score
Here is the formula to calculate Z-Score.
z = (X – μ) / σ
Let’s dive deeper into the parts of this formula.
X is a single raw data value.
μ is the mean of the dataset.
σ is the standard deviation of the dataset.
Before we begin we will need a group of data to calculate Z-Score in Excel.
Step 1
First, you need to have a clean and tidy group of data to work with.
Step 2
In this example, we want to calculate the Z-Score for the data group. To do that, we need to calculate the mean and standard deviation for this group of data. Then, we will need to insert the formula to calculate the Z-Score.
Here are the respective formulas.
Mean = AVERAGE(A2:A15)
Standard Deviation = STDEV(A2:A15)
Z-Score = (A2 – $D$1)/D2
Step 3
Once you have pressed ‘Enter’, you will get something like this.
Step 4
To apply the formula to all of the data, you can simply drag the right-bottom corner downwards.
