Z-Score in Excel

It’s easy to calculate Z-Score in Excel. Let’s learn how.

Z-Scores are a common value in statistics. A Z-Score shows how many standard deviations away from the mean (average) a given value is.

How to Calculate Z-Score in Excel

Let’s start with some columns of data like this. Here, we’re using sample points in a tournament, but you can use Z-Scores to analyze all kinds of data.

First, you’ll need to perform a couple of quick calculations. Begin by finding the mean, or average, of the numbers. Click into an empty cell. On the Ribbon, go to Formulas > More Functions > Statistical, then choose Average from the dropdown.

 

With the text in the Number1 field selected, click and drag to highlight your data. You’ll see the Number1 field update to read B2:B6. Then press OK. Excel will return the average of the five scores.

Next, you’ll need to find the Standard Deviation. The steps are similar. In the next cell, again go to Formulas > More Functions > Statistical, but this time, select STDEVPA from the dropdown.

Again, click and drag to select your data range, then click OK. The Standard Deviation value will compute.

Now, it’s time to find your actual Z-Score values. Return to the familiar menu sequence in a new cell, Formulas > More Functions > Statistical, this time choosing STANDARDIZE.

A menu will appear. In the three fields, enter the cell reference of your first data point in X. Reference your average cell in the Mean field, and your standard deviation in the last one.

Click OK, and your Z-Score will calculate for the first data point. Lock in your cell references to the Average and Standard Deviation cells, then click and drag the edge of the cell to automatically calculate Z-Scores for the rest of your data.

As you can see, Microsoft Excel makes statistics a breeze, thanks to powerful functions and simple formulas.