How to Find Z score in Excel: Step-by-step Guide

A Z score shows how far a data point is from the mean of a dataset in terms of standard deviations. It helps you understand whether a value is above or below average and by how much. Z scores are widely used in statistics to compare values from different datasets or to identify outliers. Finding Z scores in Excel is easy because Excel has built-in functions for calculating the mean and standard deviation. You can quickly apply the Z score formula to any dataset using simple formulas. In this guide, you’ll learn exactly how to find a Z score in Excel step-by-step, with formulas, examples, and shortcuts.

The Z Score Formula

The Z score formula is very simple. It is written as:

Z = (X – μ) / σ

Where:

• Z is the z-score, or standardized score
• X is the individual data point or raw score you want to evaluate
• μ is the mean of the population or sample
• σ is the standard deviation of the population or sample 

The z scores tells you how many standard deviations a value is from the mean.

For example, if a student scored 85 in a test where the mean score is 75 and the standard deviation is 5, the Z score would be:

Z = (85 – 75) / 5

= 2.0

This means the student’s score is 2 standard deviations above the mean. If the value was negative, eg. -2, then the mean of the student would be 2 standard deviations below the mean.

Want to quickly compute z score for any single data point? Try our z score calculator for instant results. Our z score probability calculator also allows you to compute cumulative probabilities from a standard normal distribution.

Computing Z score in Excel: Step-by Step

Finding Z scores in Excel is simple once you understand the formula. Excel makes it easy to perform these calculations using built-in functions for the mean and standard deviation. Whether you’re analyzing exam results or comparing measurements, the steps below will show you exactly how to calculate Z scores quickly and accurately. Let’s go through the process step-by-step.

Step 1: Enter Your Data in Excel

Start by entering your dataset in a single column. For example, let’s say you have exam scores for 10 students. The data should be entered in excel as shown below:

Type these values into cells A2:A11 in Excel. This will be your raw data that we’ll use to calculate the Z scores.

Step 2: Calculate the Mean and Standard Deviation

Next, we need to calculate the mean and standard deviation of the data.

• In cell F2, type the formula for the mean: =AVERAGE(A2:A11)
• In cell F3, type the formula for the standard deviation: =STDEV.S(A2:A11)

Note. We used STDEV.S because we’re working with a sample data. If you are working with population data, then you would need to use STDEV.P.

The results will be as shown below:

The mean and standard deviation is 87.5 and 15.14, respectively.

Step 3: Apply the Z Score Formula in Excel

Now, calculate the Z score for each data point. This will involve subtracting the mean from each of the data point and dividing the results by the standard deviation. Specifically, in cell B2 (next to the first score), type:

=(A2 – $F$2)/$F$3

Note. This formula subtracts the mean from each score and divides the result by the standard deviation. The dollar signs keep the mean and standard deviation cells fixed when copying the formula.

Thus, the results of the first cell (B2) in z score column should be as shown below:

Step 4: Find the z score of the other values

To find the z scores of the other values, you only need to copy the formula by dragging the formula down from B1 to B11. This calculates the Z scores for all your data points and the results will be as shown below:

Step 5. Interpret the Results

From the calculated z-scores, it is clear that some are negatives and others positives. The negative ones indicate that the exam scores for the students were below the mean, while the positive ones indicate that the exam scores were above the mean.

For instance, the first student with a score of 65 had a Z score of -1.49. This implies that the student’s score was about 1.5 standard deviations below the average. Similarly, a student with a score of 100 had a z-score of 0.83, indicating that the score was about 0.83 units above the mean.

Alternative Method for Finding Z score in Excel

Excel has a built-in shortcut that does the Z score calculation for you. The function is:

=STANDARDIZE(X, Mean, Standard_dev)

For example, if the score is in A2, the mean is in F2, and the standard deviation is in F3, use:

=STANDARDIZE(A2, $F$2, $F$3)

This returns the same result as the manual formula =(A2 - $F$2)/$F$3. In other words, STANDARDIZE is simply a shortcut for Z = (X – μ) / σ.

Use dollar signs ($F$2, $F$3) to lock the mean and standard deviation cells before copying the formula down. The STANDARDIZE function is cleaner and reduces formula errors. It is especially handy when you compute Z scores for many rows or when you prefer a single, readable function instead of building the formula yourself.

Conclusion

Calculating Z scores in Excel is straightforward once you understand the basic formula and Excel’s built-in functions. By following the steps in this guide, you can easily find how far each data point lies from the mean, whether it’s above, below, or exactly average. Excel’s flexibility allows you to calculate Z scores manually or use the faster STANDARDIZE function for quick results. Mastering this process not only strengthens your understanding of data analysis but also helps you make accurate comparisons and identify outliers in your dataset.

Frequently Asked Questions