Finding ways to make sense of the data you have is the whole point of working with statistics, and Microsoft Excel is a powerful tool for doing just that. The z-score is a simple statistical measure that tells you how many standard deviations one data point is from the sample mean. This makes it a lot easier to see how different numbers compare to the average for the group. No matter what version of Excel you use or how big your dataset is, it’s easy to figure out a z-score with Excel.
Setting Up the Numbers
Before using Excel as a z-score calculator, it’s best to put the most important data in a single column and make sure there’s enough room for the other pieces of information you need. Using the example of a class of 20 students who have all taken a test, you could make a list of the students’ names in one column (say, column A, from row 2 to 21), their scores in the next column (column B), and a blank space for each z-score in the column next to it (C). You also need a single cell for the set’s mean and standard deviation, which you can put anywhere, but for this example, they are in cells D2 and E2, respectively.
How to figure out the mean and standard deviation
Excel has built-in functions that can fill in the mean and standard deviation of your data. Go to the cell you left for the mean and type “=AVERAGE(range),” where “range” is the range of cells with the data (like test scores). In the example, you would type “=AVERAGE(B2:B21),” but after opening the parentheses, you can also use your mouse to select the cells. This gives you the data’s mean.
For versions of Excel before 2010, type “=STDEV(range)” to find the standard deviation of the data. For versions of Excel after 2010, type “=STDEV.S(range)” or “=STDEV.P(range)”. The “S” version is for data that only shows a small part of the whole population. For example, if you only have data from one class but want to draw conclusions about the whole school’s scores, you would use the “S” version. The “P” version is for data from the whole population, when you’re only interested in your class, or if you have all the scores from the school. Again, where it says “range,” type or click the range of cells that hold your data. In the example, you would type “=STDEV.P(B2:B21).”
How to Use the Z-Score Formula
Excel makes it easy to figure out the z-score with a simple formula. The formula is: Z = (X − x̄) ÷ σ , where X is the data point in question, x is the mean of the sample, and is the standard deviation.
With the way the cells are set up in Excel so far, this is easy to figure out. Go to the cell next to your first data point, which in this case is B2’s cell C2. Type “=(B2-$D$2)/$E$2” to tell it to subtract the mean from the data point and divide the result by the standard deviation. Press “Enter” to confirm. In words, the formula is “=([data point] – [mean]) / [standard deviation]” with the relevant cell references in place of the square brackets.
In the example, the dollar signs ($D$2 and $E$2) tell Excel to keep those exact cells when the formula is moved down. You can copy the formula to the whole z-score column by clicking the bottom right corner of the cell with the formula and dragging it down. This changes the first cell reference so that it points to the data point directly to the left. It also gives back a list of z-scores that tell you how many standard deviations each result is from the mean. A score below the average is shown by a negative result, while a score above the average is shown by a positive result.