How to Calculate Z Score in Excel: What You Need to Know

Basically, a z score can tell you how close or how far your highest or lowest data points are from the average. This is important because when it comes to interpreting your data, the z score tells you how good your data is. When your data points are too far from the z score - meaning when the standard deviation is -3 or +3 - that means you can safely assume that your data points show that there is a problem with your prediction, whatever it is. A z score is a useful tool that is there to tell you how extreme your data points are.

Repetition makes for memorization. For the sake of making this as clear as possible, the final definition of a z score will be even more simple: a z-score is the standard by which you measure your data. It's there for the sake of comparison, like the numeral equivalent of a devil's advocate.

Z scores are a way to compare results from a test to a normal population. Results from tests or surveys have thousands of results and units. However, those results can often seem meaningless, just a mishmash of numbers that don't mean anything. For example, knowing that a house cat typically weighs 8 pounds is great, but if you want to compare it to the “average” cat's weight, looking at a table of data can be overwhelming. A z score can tell you how that cat's weight is compared to the average population's mean weight, and then you can decide if your cat needs to lose weight or not.

The basic z score formula for a sample is:
z = (x – μ) / σ

This is a formula that assumes a normal distribution for the z score is possible. In this formula, x represents the selected data point from your data set, the μ is the mean of your data points and the σ is your standard deviation. This formula is good to use for proving your predictions correct.

When you have multiple samples and want to describe the standard deviation of those sample means (the standard error), you will use this z score formula:
z = (x – μ) / (σ / √n)

This z score will tell you how many standard errors there are between the sample mean and the population mean. This z score formula essentially tells you how probable it is to find a sample that is above the mean of your sample population - or, in other words, how often your standard deviation will be disproven.

Start from the top and open Microsoft Excel on your computer. Excel is the application that has an icon of a green sheet with an "X" in front of a table. Open the Excel file with the dataset you want to find the z scores for, otherwise, input your data into the first column (A) of a new blank Excel document. Make sure your numbers are vertical and not horizontal - don't make the mistake of inputting your data into a row instead of a column. Also look over your data entry before going any further!

Enter the mean formula in a blank cell in the same column you put your data points in. When you have all your data points recorded in an Excel spreadsheet, you can calculate the mean using this formula: =AVERAGE("cell range"). You will be replacing "cell range" with the range of cells that contain your data points.

So, say that your data points are from cell A1 to cell A13. in A 14, you will put =AVERAGE(A1:A13) and then you will hit ENTER on your keyboard so Excel knows to calculate that formula. It's important to remember that Excel responds only to commands that start with an equal sign (=) and that there should be no spaces in between any part of the formula. After you hit ENTER, if you've entered the mean formula correctly, Excel will replace your formula with the mean of your data points.

For data points that are recorded in an Excel spreadsheet, you can calculate the standard deviation using the formula =STDEV("cell range") in a blank cell, replacing "cell range" with the same range of cells that contain all your data points.

So, continuing with our previous example, in the A15 cell, you would put =STDEV(A1:A13) and hit ENTER. Once again, Excel will replace your formula with a number, which will be your standard deviation.