How to Calculate Z-Scores in Google Sheets

Key Insights

• Z-scores transform raw data into a standardized scale, letting you compare values across different datasets and instantly identify outliers more than 2 standard deviations from the mean.
• Google Sheets offers two approaches: manual calculation using AVERAGE() and STDEV() functions, or the cleaner STANDARDIZE() function that wraps everything into one formula.
• Always use STDEV() for samples and STDEV.P() for entire populations—using the wrong one will skew your z-scores and lead to incorrect conclusions.

Introduction to Z-Scores

Z-scores answer a simple but powerful question: how unusual is this data point? When you’re staring at a spreadsheet full of sales figures, test scores, or performance metrics, raw numbers only tell part of the story. A salesperson closing $50,000 in deals sounds impressive—until you realize the team average is $80,000.

Z-scores standardize your data by expressing each value as the number of standard deviations it falls from the mean. This transformation serves three critical purposes: comparing values from different datasets (like test scores from different exams), identifying outliers that warrant investigation, and preparing data for statistical analysis that assumes standardization.

Whether you’re an analyst building dashboards or a manager trying to make sense of quarterly reports, z-scores belong in your toolkit. Google Sheets makes calculating them straightforward—once you understand what’s happening under the hood.

Understanding the Z-Score Formula

The z-score formula is deceptively simple:

z = (x - μ) / σ

Let’s break down each component:

• x = the individual value you’re analyzing
• μ (mu) = the mean (average) of your dataset
• σ (sigma) = the standard deviation of your dataset

The numerator (x - μ) tells you how far the value sits from the mean. The denominator σ scales that distance by the dataset’s spread. A z-score of 0 means the value equals the mean. A z-score of 1 means the value is one standard deviation above the mean. A z-score of -2.5 means the value is 2.5 standard deviations below.

In a normal distribution, z-scores map to predictable percentages:

• 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 why analysts commonly flag values with |z| > 2 as potential outliers—they represent roughly the most extreme 5% of observations.

Manual Z-Score Calculation Using Basic Functions

The most transparent way to calculate z-scores in Google Sheets uses the AVERAGE() and STDEV() functions directly. This approach makes your logic explicit and easy to audit.

Assume your data lives in column A, with values in cells A2 through A100. In cell B2, enter:

=(A2-AVERAGE($A$2:$A$100))/STDEV($A$2:$A$100)

The dollar signs create absolute references. When you drag this formula down to B3, B4, and beyond, the A2 reference updates to A3, A4, etc., while $A$2:$A$100 stays locked. This ensures every z-score calculation uses the same mean and standard deviation.

Here’s what each piece does:

=( A2                           # Current value (relative reference)
   - AVERAGE($A$2:$A$100)       # Mean of all values (absolute reference)
 ) / STDEV($A$2:$A$100)         # Standard deviation (absolute reference)

To apply this to your entire dataset, enter the formula in B2, then drag the fill handle (small blue square in the cell’s corner) down through B100. Alternatively, select B2:B100 and press Ctrl+D (Cmd+D on Mac) to fill down.

This manual method has one advantage: transparency. Anyone reviewing your spreadsheet immediately understands the calculation. The downside is formula length and the risk of reference errors.

Using STANDARDIZE() for Cleaner Calculations

Google Sheets provides a built-in STANDARDIZE() function that encapsulates the z-score formula. The syntax is:

=STANDARDIZE(value, mean, standard_deviation)

For our dataset in column A, the formula becomes:

=STANDARDIZE(A2, AVERAGE($A$2:$A$100), STDEV($A$2:$A$100))

This produces identical results to the manual method. The function simply performs (value - mean) / standard_deviation internally.

Which should you use? I recommend STANDARDIZE() for most cases. It’s self-documenting—anyone familiar with statistics recognizes the function name. It also reduces parentheses errors in complex formulas.

However, the manual method wins when you need to debug calculations or when you’re teaching someone the underlying concept. Sometimes explicit is better than implicit.

Practical Example: Analyzing Sales Performance

Let’s work through a realistic scenario. You manage a sales team of 20 representatives and need to identify top performers and those needing support. Your data looks like this:

First, create named ranges for readability. Select B2:B21, then go to Data > Named ranges and create a range called SalesData. This makes formulas self-explanatory.

In cell C2, calculate the z-score:

=STANDARDIZE(B2, AVERAGE(SalesData), STDEV(SalesData))

Drag this formula down through C21. Your spreadsheet now shows each rep’s standardized performance.

To quickly identify outliers, add conditional formatting. Select C2:C21, then go to Format > Conditional formatting. Create two rules:

Rule 1: High performers (z > 2)

Custom formula: =C2>2
Format: Green background

Rule 2: Underperformers (z < -2)

Custom formula: =C2<-2
Format: Red background

Now exceptional performance jumps off the screen. A rep with a z-score of 2.3 isn’t just above average—they’re statistically exceptional, outperforming roughly 99% of their peers.

For a summary view, add these formulas:

# Count of high performers
=COUNTIF(C2:C21, ">2")

# Count of underperformers  
=COUNTIF(C2:C21, "<-2")

# Average z-score (should be ~0)
=AVERAGE(C2:C21)

Common Pitfalls and Best Practices

Population vs. Sample Standard Deviation

This is the most common mistake. Google Sheets offers two standard deviation functions:

• STDEV() or STDEV.S() — for samples (divides by n-1)
• STDEV.P() — for entire populations (divides by n)

If your data represents a sample from a larger population (most business scenarios), use STDEV(). If your data is the complete population (all employees, all transactions ever), use STDEV.P(). When in doubt, use STDEV()—the sample version is more conservative.

Using the wrong function inflates or deflates your z-scores, potentially misclassifying outliers.

Handling Blanks and Zeros

AVERAGE() and STDEV() ignore blank cells but include zeros. If zeros represent missing data rather than actual zero values, replace them with blanks or use AVERAGEIF() and a custom standard deviation calculation:

=AVERAGEIF(A2:A100, "<>0")

Non-Normal Distributions

Z-scores assume your data follows a roughly normal (bell-shaped) distribution. For heavily skewed data—like income, website traffic, or anything with a long tail—z-scores can be misleading. A z-score of 3 doesn’t mean “top 0.1%” if your distribution isn’t normal.

Before relying on z-scores, create a histogram of your data (Insert > Chart > Histogram). If it’s severely skewed, consider log-transforming the data first or using percentile ranks instead.

Troubleshooting Common Errors

• #DIV/0! — Your standard deviation is zero, meaning all values are identical. Z-scores don’t apply.
• #VALUE! — Non-numeric data exists in your range. Clean your data or use ISNUMBER() checks.
• Unexpected results — Check for mixed absolute/relative references. One misplaced dollar sign breaks everything.

Conclusion

Z-scores transform raw numbers into actionable insights. You now have two methods at your disposal: the transparent manual calculation using AVERAGE() and STDEV(), and the streamlined STANDARDIZE() function. Both produce identical results—choose based on your audience and debugging needs.

Start applying these techniques to your own data. Identify which sales reps, products, or time periods deviate significantly from the norm. Use conditional formatting to make outliers visible at a glance. Remember to verify your data approximates a normal distribution before drawing strong conclusions.

Once you’re comfortable with z-scores, explore related concepts: hypothesis testing uses z-scores to determine statistical significance, and comparing z-scores across different metrics lets you build composite performance indices. The standardized scale you’ve learned here forms the foundation for much of applied statistics.