How to Calculate the Z Score in Excel (Step-by-Step Guide)

The z score is one of those statistical concepts that sounds intimidating but becomes straightforward once you see what it actually measures. If you work with data in Excel — whether for school, research, quality control, or business analysis — knowing how to calculate z scores gives you a reliable way to understand where any individual value stands relative to the rest of your dataset.

What a Z Score Actually Tells You

A z score (also called a standard score) measures how many standard deviations a data point is from the mean of its dataset. A z score of 0 means the value sits exactly at the mean. A z score of +2 means it's two standard deviations above the mean. A score of -1.5 means it's one and a half standard deviations below.

This makes z scores especially useful for:

• Identifying outliers in a dataset
• Comparing values across different scales or units
• Normalizing data before feeding it into models or further analysis
• Flagging unusually high or low results in quality testing or exam scores

The formula itself is simple:

Z = (X − μ) / σ

Where:

• X = the individual data point
• μ = the mean of the dataset
• σ = the standard deviation of the dataset

The Two Approaches in Excel

Excel doesn't have a single dedicated "ZSCORE" function, but the calculation is easy to build using built-in functions. There are two common approaches depending on whether you're working with a population or a sample.

The distinction matters. STDEV.P assumes your dataset is the entire population. STDEV.S applies Bessel's correction, which is more appropriate when your data is a sample drawn from a larger group. For most real-world analysis, STDEV.S is the safer default unless you're certain you have complete population data.

Step-by-Step: Calculating Z Scores in Excel

Step 1 — Set Up Your Data

Place your data values in a single column — for example, A2:A21 for 20 data points. Label the column in A1 ("Score," "Value," "Result," etc.).

Step 2 — Calculate the Mean and Standard Deviation

In separate cells (often placed at the top or side of your sheet):

• =AVERAGE(A2:A21) → gives you the mean
• =STDEV.S(A2:A21) → gives you the standard deviation

You can store these in named cells (e.g., D1 for mean, D2 for standard deviation) to reference them cleanly in your formula.

Step 3 — Write the Z Score Formula

In cell B2, enter:

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

The dollar signs lock the references to your mean and standard deviation cells, so when you drag the formula down the column it always references the same values. Without them, the references will shift and your calculations will break.

Step 4 — Copy Down the Column

Click on B2, then drag the fill handle down to B21 (or however far your data extends). Excel will calculate the z score for every row automatically.

📊 Using STANDARDIZE() — The Cleaner One-Line Option

Excel also offers the STANDARDIZE function, which combines the mean and standard deviation calculation into a single formula:

=STANDARDIZE(A2, AVERAGE($A$2:$A$21), STDEV.S($A$2:$A$21)) 

This is functionally identical to the manual formula but keeps everything in one line. It takes three arguments:

1. X — the value to standardize
2. Mean — calculated with AVERAGE()
3. Standard deviation — calculated with STDEV.S() or STDEV.P()

The trade-off is readability. Embedding AVERAGE and STDEV inside every row's formula can make auditing or editing more cumbersome. Pre-calculating mean and standard deviation in fixed cells, then referencing them, is often cleaner for larger datasets.

Factors That Affect How You Apply Z Scores

Not every dataset calls for the same approach, and a few variables determine which method makes most sense for your situation:

• Sample vs. population: Using STDEV.P on a sample will slightly underestimate variance and produce z scores that look more extreme than they should.
• Dataset size: Z scores on very small datasets (fewer than 10–15 values) can be misleading — standard deviation is less stable, so scores shift significantly with each new data point.
• Distribution shape: Z scores assume an approximately normal distribution. If your data is heavily skewed or has extreme outliers, z scores may not be the most reliable normalization method.
• Dynamic vs. static ranges: If your dataset grows over time (new rows added), locking references to a fixed range means your mean and standard deviation won't update. Using dynamic named ranges or Excel Tables solves this.
• Version of Excel: STDEV.S and STDEV.P replaced the older STDEV and STDEVP functions in Excel 2010. If you're working in an older environment or sharing files with legacy users, this matters.

What Your Z Scores Mean in Practice

Once you have a column of z scores, general interpretations follow a consistent pattern:

• |z| < 2: Value falls within normal range for most datasets
• |z| between 2 and 3: Noteworthy — may warrant a closer look
• |z| > 3: Often flagged as a statistical outlier 🚩

These thresholds aren't absolute rules — they're conventions based on the properties of the normal distribution. In a perfectly normal dataset, roughly 95% of values fall within ±2 standard deviations, and about 99.7% fall within ±3.

How you act on those outliers — whether you exclude them, investigate them, or treat them as significant findings — depends entirely on what your data represents and what question you're trying to answer.