Working with statistics is all about finding ways to make sense of the data you have, and Microsoft Excel is a powerful tool for doing just that. The z-score is a basic statistical measure that tells you the number of standard deviations a particular data point is away from the mean of the sample. This makes it much easier to compare various values in terms of how they relate to the average for the group. Calculating a z-score in Excel is a simple process regardless of the version of Excel you use or the size of your dataset.
Arranging the Data
Before you start with the actual calculation, it's best to arrange the core data in a single column and ensure you have room for the other bits of information you need before using Excel as a z-score calculator. Using the example of a class of 20 students who've all taken a test, you could make a list of the students names in one column (for example, column A, from row 2 to 21), their recorded scores in the neighboring column (B) and an empty space for each z-score in the column beside it (C). You also need a single cell each for the mean and standard deviation of the data set, which you can put anywhere, but for this example, they are in cells D2 and E2, respectively.
Video of the Day
Mean and Standard Deviation Calculations
Excel has built-in functions to fill in the spaces for the mean and standard deviation of your data. Go to the cell you left for the mean and type "=AVERAGE(range)" with the range of cells containing the data (test scores, for example) where it says "range." In the example, you'd type "=AVERAGE(B2:B21)" but you can also select the cells with your mouse after opening the parentheses. This returns the mean for the data.
Type "=STDEV(range)" to find the standard deviation of the data on versions of Excel before 2010, and either "=STDEV.S(range)" or "=STDEV.P(range)" on newer versions of Excel. The "S" version is for data that represents only a sample of the whole population, such as if you want to draw conclusions about the whole school's scores but only have data from one class. The "P" version is for data from the whole population, when you're interested only in your class, or if you have all the scores from the school. Again, input or click the range of cells containing your data where it says "range." In the example, you'd type "=STDEV.P(B2:B21)."
Using the Z-Score Formula
The formula for calculating the z-score in Excel is simple. With X standing in for the data point in question, x̄ for the mean of the sample and σ for the standard deviation, the formula is:
Z = (X − x̄) ÷ σ
In Excel, with the arrangement of cells established so far, this is easy to work out. Go to the cell beside your first data point, which in the example is cell C2 beside the data in B2. Type "=(B2-$D$2)/$E$2" to tell it to subtract the mean from the data point and then divide the result by the standard deviation and press "Enter" to confirm. In words, not using the example cell references, the formula is "=([data point] – [mean]) / [standard deviation]" with the relevant cell references in the 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 dragged down. Click the bottom right corner of the cell with the formula and drag it down to copy the formula to the whole z-score column. This changes the first cell reference so that it refers to the data point directly to the left and returns a series of z-scores, telling you how many standard deviations away from the mean each result is. A negative result indicates the score is below the average, while a positive result indicates it's above the average.