Icons Proudly American

24/7 Live chat
Cart Cart
0
Cart Cart
0

Z-Score in Excel: Definition, Examples

Microsoft Excel has many functions, from basic tabulation, to finance, to statistics. The Z-Score is a statistical function and Excel has a built-in tool to help you calculate it. 

In this post, we explain how to use Excel to calculate the Z-Score function.
Excel Mastermind

What is Z-Score?

The Z-Score is a statistical value used for statistical measurement. It is also known as the standard score. Simply put, a z-score tells you how far from the mean a data point is. 

Statistically, the value of a Z-Score is the measure of the number of standard deviations a raw score is below or above the population mean.

Placed on a normal distribution curve, a z-score will range from -3 standard deviations to +3 standard deviations. To use a z-score, you need to know:

  • The mean (μ)
  • The population standard deviation (σ)
  • A raw score (x) or the value to be standardized

The Z-Score Formula 

To calculate the z-score use this formula is: Z = (x-µ) / σ
Z Score Forumula

Where the arguments are:

  • Z = is the Z score value.
  • X = is the value to be standardized (the raw score/data point).
  • µ = is the population Mean of the given data set values.
  • σ = is the Standard deviation of the give data set values.

How to calculate the Z-Score in Excel

Calculating the z-score in Excel is simple, regardless of the version of Excel you use or the size of your dataset.

Note

  1. To calculate the z-score you need to already have the population mean and the standard deviation. If you only have the data set values, you need to calculate the two values first and then calculate the z-score.
  2. If you don’t know the population standard deviation or the sample size is below 6, you should use a t-score instead of a z-score.

There are two ways to calculate the Z-Score core in Excel

  1. By manually entering the Z scores formula.
  2. Using the STANDARDIZE formula in Excel.

As an example, here’s the data set that we will be using:
Example Data

Step #1:Calculate the Mean (or average)

You can easily calculate the mean using the AVERAGE formula in Excel.

  1. Go to the Formulas tab. 
  2. Click on More Functions under the Functions Library section.
    Formulas > More Functions

  3. From the dropdown list, click on the Statistical functions category.
  4. From the list of functions, click on the AVERAGE function.
    Statistical Average

  5. In the Function Arguments dialog box, enter the Range from Cells B4:B13 under field Number1 and click on OK.
    Functions Argument

  6. It will give you the Mean or Average value.
    calculated Average Value

  • The mean is 499.6 (or µ =499.6)
Alternatively: you can calculate the mean with the formula =AVERAGE(number1).

You can select the whole data set then number1 with the range of cells containing the data.

For example,  =AVERAGE(B4:B13: The mean (average) wil be499.6 (µ =499.6)

Step #2: Calculate the Standard Deviation (SD)

To calculate the SD, you can use the STDEV formula in Excel. This formula is identical to the STDEV.S formula since it calculates the SD of the sample. 

Note: if you want to calculate the SD of the population, you should use the STDEV.P formula instead.

To calculate the SD:

  1. Go to the Formulas tab. 
  2. Click More Functions under the Function Library section.
    More Functions

  3. From the drop-down list, click on the Statistical Function category.
  4. From the list of functions, click on STDEVPA.
    STDEVPA

  5. In the Function Arguments dialog box, enter the cells range from B4:B13 under field Value1 and click on OK.
    Standard Deviation

  6. This will give you the SD value
    Standard Deviation results

  • The SD (σ) = 46.2843

Alternatively: you can calculate the SD by entering the formula.

In a new cell enter the formula =STDEV(number1) and replace number1 with the range of cells containing the data (B4:B13), i.e. =STDEV(B4:B13).
  • The SD (σ) = 46.2843

Now, we have the Mean and SD. we can calculate the z-score manually in Excel.

Step #3: Calculate the Z-Score in Excel

To calculate the z-score:

  1. Go to the FORMULAS tab. 
  2. Under the Functions Library, click on More Functions
    Excel Functions

  3. From the drop-down list, click on the Statistical Function category.
  4. From the list of functions, click on the STANDARDIZE function.
    Excel statistical functions - STANDARDIZE

  5. In the Function Arguments dialog box, enter the Cell value B4 under field X.
    Functions Argument X
  6. Enter the Mean value in the second field Mean (in our case it is mentioned under Cell B15).
    Functions Argument - Mean
  7. Enter the SD value in the third field Standard_dev (in our case it is mentioned under Cell B16, then Click on OK.
    Standard-Dev
  8. This will give you the result of the z-score for the first data set
    CALCULATED SD vALUE
  9. To get the z-score values of all the other data sets, drag his formula for the rest values. The z-score values will pop-up beside every value.

Alternatively: you can calculate the z-score by entering the formula:

  1. In an empty cell, preferably next to the raw data value, enter the formula:
    =(Raw data value - Mean)/SD
  2. Replace the following in the equation with:
    1. Raw data value – This is the cell containing original data value that you want to convert to a Z score
    2. Mean – The cell containing the mean value of the data set
    3. SD – The cell containing the SD of the data set
  3. The z-score will come as -0.74755
  4. Drag the formula through all the data sets to get the rest of the z-score values 

Wrapping UP 

Here are the things to remember

  • The Z score tells us a number of standard deviations that are away from the mean of the distribution or dataset.
  • Data values which are higher than the mean, have a positive Z-score value.
  • Data values that are below the mean, have a negative Z score value.
  • The Z score value is used for statistical analysis.

In this guide to Z-Score in Excel, we’ve discussed how to calculate Z Score in excel along with practical examples. We believe it has been an insightful learning opportunity. 

If you’re looking for more guides or want to read more Excel, and tech-related articles, consider subscribing to our newsletter where we regularly publish tutorials, news articles, and guides.

Recommended Articles

You can also go through the following articles to learn more about Excel:

  1. 13 Excel Tips and Tricks to Make You Into a Pro
  2. Top 51 Excel Templates to Boost Your Productivity
  3. How to use the NPER Function in Excel
  4. The Most Useful Excel Keyboard Shortcuts
  5. How to Calculate Break-Even Analysis in Excel
  6. 7 Tips to Become an Excel Mastermind

Icons Subscribe now and get a Mystery
Mystery Offer from SoftwareKeep

Cart Cart
0