Excel: How to Find the Z-Score

Key Insights

• Z-scores transform raw data into standardized units, enabling meaningful comparisons across different scales and datasets—essential for identifying outliers and understanding where individual values fall within a distribution.
• Excel offers two approaches: manual calculation using AVERAGE and STDEV functions for transparency, or the STANDARDIZE function for cleaner formulas—choose based on whether you need to audit intermediate values.
• The choice between population (STDEV.P) and sample (STDEV.S) standard deviation directly impacts your z-scores; using the wrong one introduces systematic error into your analysis.

Introduction to Z-Scores

A z-score tells you exactly how far a data point sits from the mean, measured in standard deviations. If a value has a z-score of 2, it’s two standard deviations above average. A z-score of -1.5 means it’s one and a half standard deviations below.

This standardization solves a fundamental problem in data analysis: comparing apples to oranges. Consider evaluating employee performance across different departments where metrics vary wildly—sales figures in thousands versus customer satisfaction scores from 1 to 10. Raw numbers are meaningless for comparison. Z-scores put everything on the same scale.

Z-scores also form the foundation for identifying outliers, calculating probabilities under the normal distribution, and performing many statistical tests. If you work with data in Excel, you need this technique in your toolkit.

The Z-Score Formula

The z-score formula is straightforward:

z = (x - μ) / σ

Where:

• x = the individual data point you’re standardizing
• μ (mu) = the population mean
• σ (sigma) = the population standard deviation

The numerator (x - μ) calculates how far the value deviates from the mean. The denominator (σ) scales that deviation by the typical spread of the data. The result is a dimensionless number that tells you relative position.

For sample data, replace μ with x̄ (sample mean) and σ with s (sample standard deviation). The formula structure remains identical—only the calculation of the underlying statistics changes.

Method 1: Manual Calculation Using AVERAGE and STDEV

The manual approach breaks z-score calculation into discrete steps. This transparency helps during auditing and debugging, and it’s often clearer for stakeholders reviewing your spreadsheet.

Assume your raw data lives in column A, from A2 to A101 (100 data points, with headers in row 1).

First, calculate the mean in a dedicated cell:

=AVERAGE(A2:A101)

Place this in cell D2 and label D1 as “Mean” for clarity.

Next, calculate the standard deviation:

=STDEV.P(A2:A101)

Put this in cell E2 with “StdDev” in E1.

Now calculate the z-score for each data point. In cell B2, enter:

=(A2-$D$2)/$E$2

The dollar signs create absolute references to your mean and standard deviation cells. Drag this formula down through B101 to calculate z-scores for your entire dataset.

Your spreadsheet structure should look like this:

This method’s advantage is visibility. Anyone reviewing your work can immediately see the mean and standard deviation values, verify them independently, and understand how z-scores were derived.

Method 2: Using STANDARDIZE Function

Excel’s STANDARDIZE function collapses the z-score calculation into a single formula. It takes three arguments: the value to standardize, the mean, and the standard deviation.

For a single z-score calculation:

=STANDARDIZE(A2, AVERAGE(A2:A101), STDEV.P(A2:A101))

This produces identical results to the manual method but in one cell. The formula is self-contained—no helper cells required.

For calculating z-scores across an entire column, you can nest the statistical functions:

=STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.P($A$2:$A$101))

Note the absolute references on the range (A$2:A$101). This ensures the mean and standard deviation calculations reference the same dataset as you drag the formula down.

When should you use STANDARDIZE versus the manual approach? Use STANDARDIZE when:

• You need compact formulas
• The spreadsheet is for personal use or technical audiences
• You’re embedding z-scores in larger formulas

Stick with the manual method when:

• You need to audit or explain calculations
• Multiple analyses depend on the same mean/standard deviation
• You want to avoid recalculating statistics in every cell (performance consideration for large datasets)

Population vs. Sample Standard Deviation

This distinction matters more than many Excel users realize. Using the wrong standard deviation function systematically biases your z-scores.

STDEV.P calculates population standard deviation. Use it when your data represents the entire population you care about—all employees in a company, all transactions in a quarter, all products in inventory.

STDEV.S calculates sample standard deviation. Use it when your data is a subset meant to represent a larger population—survey responses from 500 customers representing millions, test scores from one class representing all students.

The mathematical difference involves Bessel’s correction. STDEV.S divides by (n-1) instead of n, which corrects for the bias introduced when estimating population variance from a sample.

Here’s how the results differ:

' For a dataset in A2:A101 (100 values)
=STDEV.P(A2:A101)    ' Result: 18.29
=STDEV.S(A2:A101)    ' Result: 18.38

The sample standard deviation is always slightly larger. This difference shrinks as sample size increases but remains meaningful for smaller datasets.

The impact on z-scores:

' For a value of 95 with mean of 67.5
' Using population StdDev (18.29):
=(95-67.5)/18.29     ' Z-score: 1.503

' Using sample StdDev (18.38):
=(95-67.5)/18.38     ' Z-score: 1.496

A 0.007 difference might seem trivial, but it compounds across analyses. If you’re setting thresholds for outlier detection or calculating percentiles, consistent methodology matters.

Rule of thumb: If you’re unsure, ask yourself: “Does this data represent everything, or a sample of something larger?” When in doubt with business data, STDEV.S is usually the safer choice.

Practical Application: Calculating Z-Scores for a Dataset

Let’s walk through a complete example. You have monthly sales figures for 50 salespeople and need to identify top performers and underperformers using z-scores.

Set up your spreadsheet:

Step 1: Enter your z-score formula in C2:

=STANDARDIZE(B2, AVERAGE($B$2:$B$51), STDEV.S($B$2:$B$51))

Note: Using STDEV.S because these 50 salespeople likely represent a sample of potential sales performance, not the entire universe of possible outcomes.

Step 2: Copy the formula down through C51.

Step 3: Add conditional formatting to highlight outliers. Select C2:C51, then create rules:

• Values > 2: Green fill (exceptional performers)
• Values < -2: Red fill (underperformers requiring attention)

Alternative: Array Formula Approach

In newer Excel versions (365/2021), you can calculate all z-scores at once using dynamic arrays:

=STANDARDIZE(B2:B51, AVERAGE(B2:B51), STDEV.S(B2:B51))

Enter this in C2, and Excel automatically spills results through C51. This approach is cleaner and recalculates more efficiently.

For named ranges (recommended for complex workbooks):

' Define named range "SalesData" = $B$2:$B$51
' Then use:
=STANDARDIZE(SalesData, AVERAGE(SalesData), STDEV.S(SalesData))

Named ranges make formulas self-documenting and reduce errors when ranges need updating.

Interpreting Z-Score Results

Z-scores follow predictable patterns when data is normally distributed:

• 68% of values fall between z = -1 and z = +1
• 95% of values fall between z = -2 and z = +2
• 99.7% of values fall between z = -3 and z = +3

This is the empirical rule (68-95-99.7 rule), and it provides immediate context for any z-score you calculate.

Practical interpretation guidelines:

When you encounter z-scores beyond ±3, your first assumption should be data error. Check for typos, unit mismatches, or data entry mistakes before concluding you’ve found a genuine outlier.

For business applications, common threshold choices:

• Quality control: Flag items with |z| > 2
• Fraud detection: Investigate transactions with |z| > 3
• Performance reviews: Recognize employees with z > 1.5

These thresholds aren’t universal laws—adjust based on your domain knowledge and the consequences of false positives versus false negatives.

Z-scores assume roughly normal distribution. For heavily skewed data, z-scores become less meaningful. A z-score of 3 in a right-skewed distribution might not be as unusual as it would be in a normal distribution. Consider transforming your data (log transformation is common) before calculating z-scores on skewed datasets.