Free Practice WorkbookThe Z score helps you understand data in terms of its standard deviations from the mean. It allows you to identify outliers and understand the distribution. In this article, you will learn how to calculate Z scores in Excel.
Key Takeaways:
- A Z score tells us how many standard deviations a value is from the mean.
- It helps identify outliers in the data.
- Standard deviation is used to measure the data’s spread around the mean.
- Make sure that your Excel version supports the necessary statistical functions.
- Scatter plots in Excel effectively visualise Z scores.
Table of Contents
What is a Z Score?
A Z score is a statistical metric that tells me where an observation lies as compared to its average. It is calculated in a standardised way and is free of scale and unit dependencies. The Z score can either be positive or negative. Standard deviation is the cornerstone in the calculation of Z-scores. It is the unit of measurement that defines the spread of data around the mean. Z score tells you how many standard deviations a data point is from the average.
How to Calculate Z-Score in Excel
Method 1: Using AVERAGE and STDEV functions
Calculate the mean of the data by using the AVERAGE function.
Use the STDEV function to get the standard deviation.
Calculate the Z-score by subtracting the average from the data point and then dividing by the standard deviation.
Use relative and absolute cell references to provide accuracy in the dataset. By adding the $ symbol, I anchor the mean and standard deviation cells. This allows me to drag and replicate the formula across multiple cells.
Method 2: Use STANDARDIZE Function
The STANDARDIZE function returns a Z score based on the mean and standard deviation. The syntax is
=STANDARDIZE(data_point, mean, standard_deviation)
Z-Score Scatter Plot
Follow the steps below to create a scatter plot of Z scores:
- Go to the Insert tab
- Under the Charts group, select a Scatter chart.
It shows how individual data points are spread out in relation to Z-scores.
FAQs
How to calculate z-score on Excel?
To calculate a z-score in Excel, I input my data set into a column, use =AVERAGE(range) for the mean and =STDEV.S(range) for the standard deviation if it’s a sample (or =STDEV.P(range) if it’s a population). Then, I apply the z-score formula =(data_point-mean)/standard_deviation for each data point. Each step can be executed in separate cells for clarity.
How to do the z test in Excel?
To perform a z test in Excel, first calculate the z-score for your sample mean using =(sample_mean-population_mean)/(population_standard_deviation/SQRT(sample_size)). Then find the p-value with =NORM.S.DIST(z_score, TRUE). Compare the p-value to your significance level to decide whether to reject the null hypothesis.
Can Z-Scores be Used to Predict Performance?
Yes, Z-scores can forecast future performance. If Z-scores are consistently positive, it might suggest a continuing uptrend. Meanwhile, negative or volatile Z-scores could indicate a need for strategic adjustments to change future outcomes.
How to Identify Outliers Using Z-Score?
Z scores that are beyond +3 or below -3 are considered outliers.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
