How-To

How to Calculate a Z-Score in Excel

By Jeff Butts

Published December 6, 2023

If you need to calculate a Z-Score from your data, here’s how to do that in Excel.

One fundamental concept in statistics is the Z-score. You use the Z-score to standardize scores for comparison, calculate distribution probabilities, and identify outliers in your data points. If you don’t want the overhead (or expense) of using dedicated statistical analysis software, one of Excel’s numerous functions can work quite nicely for this. Let’s look at how to calculate a Z-score in Excel.

Working With Averages, Means, and Standard Deviations in Excel

Since the Z-score represents the number of standard deviations a data point is from the average or mean, we’ll need to start by calculating those values. Fortunately, Excel does include built-in functions for calculating averages.

1. Once you enter your data, use Excel’s AVERAGE function to calculate the mean of the values.
   
2. Next, Excel’s STDEVPA function is needed to determine the standard deviation.
   

With that done, we can move on to calculating the Z-Score for each of our data points.

Using Excel to Calculate a Z-Score for Statistical Analysis

The Z-score is calculated by subtracting the mean, or average, value from the data point and dividing the result by the standard deviation. In our example spreadsheet, the formula would be:

=(B2-B10)/B11

Looking at the first data point, we see the Z-score is 0.069114954. Repeating the formula down the column, you can see the Z-scores for every other value.

If you see negative numbers in the calculations, fear not. That’s perfectly normal when the value is lower than the average. A positive Z-score means the data point is that number of standard deviations higher than the mean. Conversely, a negative result indicates the data point is that the number of standard deviations is lower than the average.

Using Excel to Analyze and Visualize Statistical Data

You don’t have to shell out hundreds of dollars to do statistical analysis or help visualize that data. In fact, Microsoft has filled Excel with numerous helpful functions that can help with that. What other data manipulation do you want to learn how to do? Let us know in the comments or in our forums, and we’ll find the answer.