How to calculate z-score in Excel – step-by-step guide

Here's how you can calculate it easily!
Last Updated on May 20, 2024
Microsoft Excel logo with a green grid icon on a gradient teal background, and a small "PK guide" logo at the top featuring tips on how to calculate z-score.
You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Are you wondering how to calculate z-score in Excel? If so, you’ve landed on the right page.

In statistics, a z-score tells us how far a data point is from the mean. While Excel still doesn’t have an automatic formula for calculating a dataset’s z-score, we can use other Excel formulas to calculate the mean and then the standard deviation. 

The entire process may sound complicated, so we’re here to explain it to you through our step-by-step guide!

What is a z-score?

A z-score is a significant statistical measure often used to interpret raw data. To calculate the z-score of a dataset, you need the following calculations:

  • X = The value that needs to be standardized. In simple words, the data point.
  • µ = Mean of the dataset.
  • σ = Standard deviation of the dataset.

This is the formula for calculating the z-score:

z = (X – μ) / σ

Calculating z-score in Excel

Scenario on hand: We have an Excel dataset with the weights of chip packets in a production line.  

What we want to accomplish: In our article, we will work with a sample dataset of packet weights to calculate the z-score for each entry in the dataset. 

By calculating the z-score, the manufacturer can ensure that the machine is filling the chip packets within the desired range. We will also identify outliers in the dataset based on the z-score to show how the z-score helps identify issues in production lines. 

Here are the steps we will follow in this article:

  1. Arranging the data
  2. Creating the helper cells 
  3. Calculating the helper cells 
  4. Calculating the z-score
  5. Interpreting the z-score 

Step 1: Arranging the data

The first step is arranging the data so that all the data points are in a vertical column. Although the data doesn’t have to be labeled to calculate the z-score, it is a good practice to label your data so that your worksheet looks professional and neat. 

Here is a picture of the data set we will be using for this example:

An Excel spreadsheet displaying a dataset to calculate z-scores. The dataset comprises packet numbers and weights in grams, providing a clear example of how to calculate z-score for each packet.
Data set

In this data set, we have 20 data points. These data points refer to the weight in grams of 20 packets of chips in a filling line. 

Step 2: Creating the helper cells

To calculate the z-score, you need to calculate the μ and σ or the mean and standard deviation of the dataset. These two will be the helper cells for calculating the z-score for each data point. 

In this demonstration, we will calculate the mean and standard deviation using the formulae available in Excel. Before that, we need to create the helper cells named Average and Standard Deviation.

A dataset in Excel showing packet numbers, weight in grams, along with a highlighted section for calculating the average, standard deviation, and how to calculate z-score.
Average and Standard Deviation

Step 3: Calculating the helper cells

Now that you’re labeled the helper cells, add the formula under or beside each cell to calculate the referred values. 

How to calculate the mean

Under the cell named ‘Average,’ calculate the mean using the syntax:

=AVERAGE(<data range>)

For our dataset, here’s the formula:

=AVERAGE(C7:C26)

Here’s the formula in action:

Excel spreadsheet showing a dataset for calculating z-scores. The average weight is 251.32 grams, and columns include packet number and weight. Highlighted cell displays the formula "=AVERAGE(C1:C28)". Learn how to calculate z-score using this data effectively.
Formula

How to calculate the standard deviation

Under the cell named ‘Standard Deviation,’ calculate the standard deviation using the syntax:

=STDEV.S(<data range>)

For our dataset, here’s the formula:

=STDEV.S(C7:C26)

Here’s the formula in action:

An Excel spreadsheet showing a dataset to calculate z-scores. The data includes packet numbers and weights, with highlighted formula cells demonstrating how to calculate z-score using the average (251.3) and standard deviation (1.993947929).
Formula

Note: We used the formula STDEV.S here because our dataset includes weights for just 20 pieces from a larger population. 

For instance, if our dataset had the heights of the entire class (the entire population), we would have used the formula STDEV.P to calculate the population standard deviation. 

Step 4: Calculating the z-score

Now that we have the helper cells ready, let’s get on to calculating the z-score. For this, we first add the column z-score beside the column with the data points like this:

A spreadsheet titled "Dataset to calculate z-score in Excel" displaying packet numbers, weights, z-scores, average weight, and standard deviation. It serves as a practical guide on how to calculate z-score accurately using Excel's built-in functions.
Z-score column

There are two ways we will use for calculating the z-score. The first syntax is formula-based:

=(<datapoint>-<group mean>)/<standard deviation>

Here’s the formula in action for the first data point in our dataset:

=(C7-F7)/G7

Here’s the value we get by inputting this formula:

An Excel spreadsheet titled "Dataset to calculate z-score in Excel" contains columns for packet number, weight, z-score, average, and standard deviation. One cell is highlighted as a guide on how to calculate z-score accurately within the dataset.
Value generated

The second syntax uses the Excel function STANDARDIZE. Here’s the syntax for this function:

=STANDARDIZE(<Cell address of data point>, <Mean> , <Standard Deviation>)

Here’s the formula in action:

=STANDARDIZE(C7,F7,G7)

This formula also requires calculating the mean and standard deviation separately. So, it is not an automatic calculation of the Z-score and will yield the same result as the formula. 

Here’s a screenshot of the value this function returns.

Excel spreadsheet showing a dataset for calculating z-scores. Columns include packet numbers, weights in grams, and computed z-scores. An error in cell E7 is highlighted, illustrating a potential mistake when learning how to calculate z-scores.
Value returned

Step 5: Expanding the formula

We have now calculated the z-score for the first data point. To calculate the z-scores for the other points in the table, we could write the formula again and again. However, this would be too time-consuming and highly susceptible to human errors. So, use a neat trick to expand the formula to the rest of the table. 

Start by adding $ signs to the cell addresses of the sample average and the sample standard deviation. Here’s how we modify our first cell:

=(C7-$F$7)/$G$7

You will see that adding the $ sign doesn’t have any impact on the calculation. What this $ sign does is give absolute references to the fixed cells: average and standard deviation. 

Now, we will use the Fill Handle Tool to expand the formula to the rest of the column’s values. 

To use the fill handle tool, move your cursor to the bottom right corner of the cell with the formula. Doing this will turn your cursor into a + sign. 

Click the left mouse button when you see this sign and drag it to the bottom of the table. Here’s how it looks:

An Excel spreadsheet displaying packet number, weight in grams, z-score, average (251.32), and standard deviation (1.993934792) columns. The z-score for the first packet is highlighted and circled to illustrate how to calculate z-score accurately.
Dragging the formula down

When you drag the formula down the table using the Fill Handle tool, it automatically calculates the values for the rest of the data points in the table. 

Here’s what our completed table looks like:

Spreadsheet showing a dataset used to calculate z-scores in Excel, including columns for packet number, weight, z-score, average, and standard deviation. Learn how to calculate z-scores easily with comprehensive examples.
Completed table

Step 6: Interpreting the z-score

Now that you’ve learned how to calculate the z-score let’s learn how to interpret it. 

First, let’s understand what the z-scores mean. The z-score could be zero, positive, or negative. A negative z-score indicates that the value is lesser than the mean. A positive z-score means that the value is greater than the mean. The value of the z-score indicates how far away from the mean it is. A zero score means that the value is at the mean. 

Scenario-based interpretation

In our scenario, the production company intends to put 250 grams of chips in every bag with a variance of 2 grams. So, the acceptable weight of the packet is between 248gm and 252gm. 

In our scenario, our average is 251.32 grams. Ideally, this should be 250 grams. So, for the purpose of interpretation, we have replaced the average with 250. Because the z-score column was calculated using the average, these values also change:

Screenshot of an Excel spreadsheet titled "Dataset to calculate z-score in Excel" showing packet weights with calculated z-scores, as well as average and standard deviation values. Learn how to calculate z-score using formulas embedded within the sheet.
Value changed

The standard deviation is 1.93 grams, which is close to the ideal 2-gram standard deviation we need. To mark z-scores that are out of range, we need to specify rules to mark z-scores that exceed +1 or are lower than -1. 

  • +1 means that the weight is 1.93 grams or 1 standard deviation greater than the mean or 250+1.93 grams. 
  • -1 means that the weight is 1.93 grams or 1 standard deviation less than the mean or 250-1.93 grams.

To mark the cells, select the z-score column, then use Conditional Formatting to specify cell rules. You will find the Conditional Formatting option in the Home tab:

Excel spreadsheet screenshot showing a dataset to calculate z-scores. The dataset includes packet number, weight in grams, and z-score columns, demonstrating how to calculate z-score. The average and standard deviation are displayed.
Conditional Formatting

Specify the lower limit

For specifying the lower limit, choose the conditional formatting option Less Than:

Excel spreadsheet displaying a dataset with packet weights in grams and their corresponding z-scores. The image highlights the conditional formatting menu for data analysis options, offering a clear view on how to calculate z-score effectively.
Less Than option

Here’s the input window where we specify -1 as the lower limit:

Dialog box titled "Less Than" showing cell conditional formatting settings. It specifies cells less than -1 will have a yellow fill with dark yellow text. Buttons for OK and Cancel are at the bottom, helping you easily identify outliers when learning how to calculate z-score.
Options

Specify the upper limit

For specifying the upper limit, choose the conditional formatting option Greater Than:

A Microsoft Excel spreadsheet with data in several rows and columns. The "Conditional Formatting" dropdown is open, showing options such as "Highlight Cells Rules" and "Greater Than." Additionally, it provides a quick guide on how to calculate z-score directly within the spreadsheet.
Greater Than option

Here’s the input window where we specify 1 as the upper limit:

Dialog box for conditional formatting in Excel, showing "Greater Than" rule. The rule is set to format cells greater than 1 with a light red fill and dark red text. Options to "OK" or "Cancel". Handy for distinguishing data, especially after you learn how to calculate z-score.
Upper limit

In our dataset, 9/20 data points, or 45% of the sample, exceed the upper limit or are lower than the lower limit. That means that the machine is performing poorly and needs to be recalibrated to get more accurate filling in the packets. 

What is the formula for the Z score in Excel?

The formula for calculating z-score in Excel is: =(x-mean)/standard deviation, where x is the value in the dataset, the mean is calculated using the AVERAGE function, and the standard deviation is calculated using the STDEV.S or STDEV.P

How to use the standardized function in Excel?

The STANDARDIZE function in Excel is a simple way to calculate a dataset’s z-score. For the right calculation, input the value, dataset average, and dataset standard deviation in the formula.

Wrapping up

In our article, we explored a real-life scenario to understand how to calculate and interpret z-scores. We first explained what z-scores are, what the two values we need to calculate them are, and how you can calculate the values and z-scores in Excel.

If you want to learn more about Excel, give these guides a read:

Abdul is a tech writer and Editor for PC Guide, specializing in all things tech, gaming, and hardware.