How to Perform a Z-Test in Excel

Key Insights

• Z-tests require large samples (n > 30) and known population standard deviation—use t-tests when these conditions aren’t met
• Excel’s Z.TEST function returns a one-tailed p-value, so multiply by 2 for two-tailed tests or use manual formulas for full control
• The Data Analysis ToolPak provides the fastest path to z-test results, but understanding the underlying formulas prevents misinterpretation

Introduction to Z-Tests

A z-test is a statistical hypothesis test that determines whether two population means are different when the variances are known and the sample size is large. The test statistic follows a standard normal distribution, which is why we call it a “z” test—the z-score tells you how many standard deviations your sample mean is from the hypothesized population mean.

You’ll reach for a z-test in scenarios like these: comparing your company’s average customer satisfaction score against an industry benchmark, testing whether a manufacturing process produces parts within specification, or evaluating whether a marketing campaign changed average order value compared to historical data.

The key distinction from a t-test is practical: z-tests assume you know the population standard deviation. In reality, you rarely do. But when working with large samples or historical data where variance is well-established, z-tests provide a straightforward analytical path.

Prerequisites and Assumptions

Before running a z-test, verify these conditions:

Sample size greater than 30. The Central Limit Theorem tells us that sample means approximate a normal distribution when n > 30, regardless of the underlying population distribution. This is the minimum threshold for z-test validity.

Known population standard deviation. This is the critical assumption. If you’re estimating standard deviation from your sample, you should use a t-test instead. Z-tests are appropriate when you have historical data establishing population variance or when you’re working with standardized measurements.

Random sampling. Your sample must be randomly selected from the population. Convenience samples or biased selection invalidates the test.

Independent observations. Each data point must be independent of others. Time-series data with autocorrelation violates this assumption.

When should you use a t-test instead? Almost always, frankly. The t-test is more conservative and doesn’t require knowing population variance. Use z-tests when you have genuine population parameters from census data, long-running process control charts, or standardized test norms.

Setting Up Your Data in Excel

Organize your data in a single column with a descriptive header. Avoid blank cells within your data range—they’ll cause calculation errors.

Here’s how to calculate the essential descriptive statistics:

# Sample mean
=AVERAGE(A2:A102)

# Sample standard deviation (if needed for reference)
=STDEV.S(A2:A102)

# Sample size
=COUNT(A2:A102)

# Population standard deviation (enter your known value)
# This should come from external data, not calculated from your sample

Create a summary section in your spreadsheet:

This setup keeps your parameters visible and makes the z-test formula readable.

Performing a One-Sample Z-Test

A one-sample z-test compares your sample mean against a known or hypothesized population mean. The formula is:

z = (x̄ - μ) / (σ / √n)

Where:

• x̄ = sample mean
• μ = hypothesized population mean
• σ = population standard deviation
• n = sample size

In Excel, build this formula step by step:

# Standard error of the mean
=D4/SQRT(D3)

# Z-score (complete formula)
=(D2-D5)/(D4/SQRT(D3))

Let’s work through a concrete example. Suppose you’re testing whether your call center’s average handle time differs from the industry standard of 6 minutes. Historical data establishes the population standard deviation at 1.5 minutes. You sample 50 calls and find an average handle time of 5.7 minutes.

# Cell setup
# D2: 5.7 (sample mean)
# D3: 50 (sample size)
# D4: 1.5 (population standard deviation)
# D5: 6 (hypothesized mean - industry standard)

# Z-score calculation
=(5.7-6)/(1.5/SQRT(50))
# Result: -1.414

The negative z-score indicates your sample mean is below the hypothesized mean. Whether this difference is statistically significant depends on your p-value calculation, which we’ll cover shortly.

Performing a Two-Sample Z-Test

Excel provides two approaches for two-sample z-tests: the Z.TEST function and the Data Analysis ToolPak.

Using Z.TEST Function

The Z.TEST function tests whether the sample mean differs from a hypothesized value:

=Z.TEST(array, hypothesized_mean, [sigma])

This function returns the one-tailed p-value. For our call center example:

=Z.TEST(A2:A52, 6, 1.5)
# Returns the probability of observing a sample mean at least as extreme as yours

Critical note: Z.TEST returns a one-tailed p-value. If your sample mean is less than the hypothesized mean, it returns the left-tail probability. If greater, it returns the right-tail probability. For a two-tailed test, you need additional logic:

# Two-tailed p-value from Z.TEST
=2*MIN(Z.TEST(A2:A52, 6, 1.5), 1-Z.TEST(A2:A52, 6, 1.5))

Using the Data Analysis ToolPak

For comparing two independent samples, the ToolPak is more straightforward:

1. Enable the ToolPak: File → Options → Add-ins → Analysis ToolPak → Go → Check the box
2. Access it: Data tab → Data Analysis → z-Test: Two Sample for Means

The dialog requires:

• Variable 1 Range: Your first sample data
• Variable 2 Range: Your second sample data
• Hypothesized Mean Difference: Usually 0 (testing if means are equal)
• Variable 1 Variance (known): Population variance for group 1
• Variable 2 Variance (known): Population variance for group 2
• Alpha: Your significance level (typically 0.05)

The output includes:

• Mean and variance for each sample
• Observations count
• Hypothesized mean difference
• Z statistic
• P-values for one-tail and two-tail tests
• Critical values for one-tail and two-tail tests

Interpreting Results and P-Values

The z-score tells you how many standard deviations your sample mean is from the hypothesized mean. But statistical significance depends on the p-value.

Calculating P-Values Manually

For a one-tailed test (testing if sample mean is greater than hypothesized):

# Right-tailed p-value
=1-NORM.S.DIST(z_score, TRUE)

# Example with z = 1.96
=1-NORM.S.DIST(1.96, TRUE)
# Result: 0.025

For a one-tailed test (testing if sample mean is less than hypothesized):

# Left-tailed p-value
=NORM.S.DIST(z_score, TRUE)

# Example with z = -1.414
=NORM.S.DIST(-1.414, TRUE)
# Result: 0.0787

For a two-tailed test (testing if sample mean differs in either direction):

# Two-tailed p-value
=(1-NORM.S.DIST(ABS(z_score), TRUE))*2

# Example with z = -1.414
=(1-NORM.S.DIST(ABS(-1.414), TRUE))*2
# Result: 0.1573

Making Decisions

Compare your p-value to your significance level (alpha):

• p < 0.05: Reject the null hypothesis. The difference is statistically significant at the 5% level.
• p < 0.01: Strong evidence against the null hypothesis.
• p ≥ 0.05: Fail to reject the null hypothesis. Insufficient evidence of a difference.

In our call center example, the two-tailed p-value of 0.1573 exceeds 0.05. We cannot conclude that the call center’s handle time significantly differs from the industry standard.

Critical Value Approach

Alternatively, compare your z-score to critical values:

# Critical value for alpha = 0.05, two-tailed
=NORM.S.INV(0.975)
# Result: 1.96

# Critical value for alpha = 0.05, one-tailed
=NORM.S.INV(0.95)
# Result: 1.645

If your absolute z-score exceeds the critical value, reject the null hypothesis.

Common Errors and Troubleshooting

Using z-tests with small samples. With n < 30, the Central Limit Theorem doesn’t reliably apply. Switch to a t-test, which accounts for the additional uncertainty in small samples.

Estimating population standard deviation from your sample. If you calculate standard deviation from your sample data and plug it into a z-test, you’re violating the core assumption. Use =T.TEST() or the t-Test tools in the ToolPak instead.

Confusing one-tailed and two-tailed tests. A one-tailed test asks “Is the mean greater than X?” or “Is the mean less than X?” A two-tailed test asks “Is the mean different from X?” Choose based on your research question before seeing the data—not after.

Misreading Z.TEST output. Remember that Z.TEST returns a one-tailed p-value. If you’re conducting a two-tailed test, you must adjust the result.

Ignoring practical significance. A statistically significant result isn’t necessarily meaningful. A 0.1-minute difference in call handle time might be statistically significant with a large enough sample, but it’s operationally irrelevant.

Assuming normality without checking. While the Central Limit Theorem helps with large samples, extremely skewed distributions may still cause problems. Create a histogram of your data to verify approximate normality.

Z-tests are powerful when their assumptions hold. Verify your conditions, choose the right test type, and interpret results in context. Excel makes the mechanics straightforward—your job is ensuring the statistics match the question you’re actually trying to answer.