We get it. Statistics can be intimidating. All those numbers and all those graphs and all of the rules on how to interpret everything - it's enough to make your head spin. Which is why we're happy to tell you that some things can be less stressful with the use of modern technology. Specifically, the little helper we're talking about is - no surprise - Microsoft Excel. Keep scrolling to learn everything you need to know about how to calculate z score in Excel. You'll be glad you did. It's easier than you think to learn how to calculate z score in Excel!
First things first! We need to break down the whole concept of a z score. Getting some clarity on the topic will make it that much easier to learn how to calculate a z-score in Excel when the time comes. You have to learn to walk before you can run, right?
What Is a Z Score?
In statistics, a z score is the golden child of standard deviations. A z score is a number that acts as a relative number that exists for the sole purpose of comparing random numbers to some kind of average. The z-score is that average, which means that learning how to calculate z score in Excel is really just a way of calculating a fancy average. And you've been calculating averages since grade school - so there's no reason to be intimidated!
The more technical definition of a z score represents the number of standard deviations from the mean a data point is, so a z score measures how many standard deviations below or above the population mean a raw score is. Z scores range from -3 standard deviations (which would fall to the far left of the normal distribution curve) up to +3 standard deviations (which would fall to the far right of the normal distribution curve).
What Does a Z Score Do?
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.
Easy Real World Z Score Example
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 Z Score Formula
Actually, depending on what you're using the z score for, there are two different z score formulas to choose from - and choosing the right formula is important. But rest assured that for any z score formula you will need two numbers: the mean (μ) and also the population standard deviation (σ). You will need both of these when you learn how to calculate the z-score in Excel.
The Basic Z Score Formula
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.
Standard Error of the Mean
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.
How to Calculate the Z Score in Excel
Now that you know what a z score is and the basic formula you will be using, it's time to learn how to calculate z score in Excel. Be prepared by having your data points easily accessible and be forewarned that there will be a lot of data entry. Just as a reminder - double check your numbers and always calculate your z score twice!
Step 1: Open Excel
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!
Step 2: Enter the Mean Formula
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.
Step 3: Enter the Standard Deviation Formula
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.
Step 4: Finding the Z-Score
Now that you've come this far, it's time to finally learn how to calculate a z score in Excel. In a blank cell next to the cell of the data point you want to find the z score of, enter the formula =(datapoint-$mean)/$standard deviation, replacing "data point" with the cell of the data point, and replacing "mean" and "standard deviation" with the absolute cell locations.
So, say you want to find the z score of the data in the A8 cell. What you would do is click on the blank B8 cell and input =(A8-$A$14)/$A$15 and then hit ENTER to calculate the z score. Again, you will be typing in the equal sign, open parentheses, the cell that has the desired data point, the minus sign, a dollar sign, the column of the mean score, another dollar sign, the row of the mean score, close parentheses, a forward slash, a dollar sign, the column of the standard deviation, another dollar sign, the row of the standard deviation, and then finally ENTER.
As a point of note, the dollar signs in the z score formula are the way that Excel knows that the numbers in your mean formula and standard deviation formula cells will remain constant even if you use those cells over and over to calculate the z score for all of your data points.
See how easy that was? Learning how to calculate z score in Excel is a cakewalk because Excel does all the really hard work for you. Of course, the job of interpreting what the z score means still falls to your shoulders, but at least you don't have to make your head ache by hand-writing and calculating the z score formula. Simply use Excel to find your mean formula and your standard deviation, and then you're only one simple input away from finding the z score for any point of data you want.
Still not seeming so simple? Try it for yourself! Trust us, it gets easier after inputting the formula a few times!