How to Compare Two Proportions Using a Z-Test in Excel

A Two Proportion Z-Test is specifically used to test for a difference between two unknown population proportions based on observed sample data. This is a critical statistical technique whenever comparing success rates, percentages, or frequencies across two separate groups, such as comparing voter turnout between two states or product failure rates between two manufacturing shifts. The test assumes that the samples were drawn independently and that the sample sizes are large enough to ensure the sampling distribution of the difference in proportions is approximately normal.

Consider a practical scenario: Suppose a superintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in School 1 is statistically the same as the percentage in School 2. This claim establishes the status quo that we intend to challenge using statistical evidence. The population proportions, P1 and P2, represent the true, but unknown, percentages of students in each school who favor chocolate milk.

To properly test this claim, an independent researcher must obtain a rigorous, simple random sample of students from each school. For instance, if the researcher selects 100 students from School 1 (n1=100) and 100 students from School 2 (n2=100) and surveys their preferences. The results might show that 70% of students prefer chocolate milk in School 1 (p1=0.70) and 68% of students prefer chocolate milk in School 2 (p2=0.68). The difference of 2% must then be rigorously evaluated to see if it is merely due to sampling variability or if it reflects a genuine difference in the populations.

We must use a two proportion z-test to formally assess whether this observed difference of 2% is statistically significant. The methodology ensures that we do not mistakenly conclude a difference exists when it does not (Type I Error), nor fail to identify a true difference (Type II Error). This test provides the quantitative proof needed to either support or reject the superintendent’s initial claim regarding equal preferences across the two schools.

Foundational Steps for Executing a Two Proportion Z-Test

The execution of a two proportion z-test follows a standardized process involving hypothesis formulation, calculation of the test statistic, and determination of the associated probability. Understanding these steps is crucial whether calculating manually or using a spreadsheet program like Excel.

Step 1. State the Hypotheses. The first and most critical step is defining the null hypothesis (H0) and the alternative hypothesis (Ha). The null hypothesis always represents the assertion of no effect or no difference, while the alternative hypothesis represents the researcher’s claim or the possibility of a genuine difference.

For a two-tailed test, which simply looks for any difference:

• The null hypothesis (H0): P₁ = P₂ (The proportions are equal.)
• The alternative hypothesis (Ha): P₁ ≠ P₂ (The proportions are not equal.)

Step 2. Calculate the Test Statistic and the Corresponding P-Value. The calculation phase is where the observed sample data is used to generate the evidence. This involves calculating the pooled sample proportion and then using that value to find the Z-score.

The Role of the Pooled Sample Proportion

Before calculating the Z-score, we must determine the pooled sample proportion, often denoted as P-bar (p). When performing a Z-test under the assumption that the null hypothesis (P1 = P2) is true, we must combine the data from both samples to get a better estimate of the single common population proportion. This pooling provides a more robust estimate of the overall success rate across both groups.

The formula for the pooled sample proportion (p) is a weighted average based on the number of successes in each sample relative to the total number of observations:

p = (p₁ * n₁ + p₂ * n₂) / (n₁ + n₂)

Using the school milk example where p1=0.70, n1=100, p2=0.68, and n2=100:

p = (.70 * 100 + .68 * 100) / (100 + 100) = (70 + 68) / 200 = 138 / 200 = .69

This pooled proportion (0.69) then replaces P1 and P2 in the standard error calculation, assuming P1 = P2 under the null condition. This ensures that the test statistic calculation is performed against a single, combined population proportion estimate, strengthening the validity of the Z-score derived from the difference.

Calculating the Test Statistic (Z-Score)

The test statistic, or Z-score, quantifies the difference between the observed sample proportions (p1 – p2) in terms of the number of standard errors. A larger absolute Z-score indicates a greater deviation from the null hypothesis, suggesting that the observed difference is unlikely to have occurred by chance.

The formula to find the Z-score utilizes the pooled proportion (p) in the denominator to estimate the standard error of the difference between the two proportions:

z = (p₁ – p₂) / √[p * (1 – p) * ( (1/n₁) + (1/n₂) )]

Continuing with our example:

z = (.70 – .68) / √[.69 * (1 – .69) * ( (1/100) + (1/100) )]

z = .02 / √[.69 * .31 * (0.01 + 0.01)]

z = .02 / √[.004278] ≈ .02 / .0654 = .306

Once the Z-score is calculated, we use the standard normal distribution to find the P-value associated with this test statistic. For a two-tailed test, the P-value represents the probability of observing a difference as extreme or more extreme than .02 in either direction, assuming the null hypothesis is true. Using the Z-score of .306 and applying a two-tailed calculation yields a P-value of approximately 0.759.

Interpreting the P-Value and Significance Level

The final stage of the hypothesis test involves comparing the resulting P-value against the predetermined significance level (alpha, often denoted as α). The significance level represents the threshold for rejecting the null hypothesis—it is the maximum probability of making a Type I error (rejecting H0 when H0 is actually true) that we are willing to tolerate.

Common choices for the significance level include 0.01, 0.05, and 0.10. For our example, we commonly adopt α = 0.05. The decision rule is straightforward: If the P-value is less than the significance level (P-value < α), we reject the null hypothesis. If the P-value is greater than or equal to the significance level, we fail to reject the null hypothesis.

In the milk preference case, the calculated P-value (0.759) is significantly larger than our chosen alpha level (0.05). Therefore, we fail to reject the null hypothesis. This statistical conclusion implies that the observed 2% difference in preference between School 1 and School 2 is not statistically significant and could easily be attributed to random sampling variation. We lack sufficient statistical evidence to claim that the true proportion of students preferring chocolate milk is different between the two schools.

How to Perform a Two Proportion Z-Test in Excel

While manual calculations help cement the understanding of the underlying statistical principles, using Microsoft Excel streamlines the process considerably, allowing for rapid and accurate computation. The following examples provide detailed instruction on setting up the calculations within an Excel worksheet for both two-tailed and one-tailed tests.

Case Study 1: Two-Tailed Z-Test Implementation in Excel

Revisiting the superintendent’s claim: A superintendent claims that the percentage of students preferring chocolate milk is the same for School 1 and School 2. The researcher gathers the data: n1=100, p1=0.70; n2=100, p2=0.68. The task is to determine if we can reject the claim of equality using a significance level of 0.05. This test seeks a difference in either direction (P1 > P2 or P1 < P2), making it a two-tailed test.

The question being tested is: Based on these results, can we reject the superintendent’s claim that the percentage of students who prefer chocolate milk is the same for school 1 and school 2? Use a .05 level of significance.

The following screenshot demonstrates the precise setup for performing this two-tailed two proportion z-test in Excel, including the corresponding formulas used for automated calculation:

Users must manually input the sample data into cells B1:B4 (Proportion 1, Sample Size 1, Proportion 2, Sample Size 2). The resulting values in cells B6:B8 (Pooled Proportion, Z Test Statistic, P-value) are then automatically calculated using the complex statistical formulas shown in the adjacent cells C6:C8.

The formulas shown execute the following essential calculations:

• Formula in cell C6: This calculates the pooled sample proportion (p) using the weighted average formula: p = (p₁ * n₁ + p₂ * n₂) / (n₁ + n₂).
• Formula in cell C7: This calculates the test statistic z using the complete formula: z = (p₁-p₂) / √p * (1-p) * [ (1/n₁) + (1/n₂)], where p is the pooled sample proportion calculated in B6.
• Formula in cell C8: This calculates the two-tailed P-value. It uses the Excel function NORM.S.DIST(B7, TRUE), which returns the cumulative probability for the standard normal distribution (mean=0, standard deviation=1). To get the two-tailed probability, we calculate the area in the tails, which is 2 * (1 – NORM.S.DIST(ABS(B7), TRUE)) or, more simply in Excel for symmetric distributions, 2 * (1 – NORM.S.DIST(B7, TRUE)) if B7 is positive, or considering the lower tail. The result 0.759 shows that the observed Z-score is very close to the mean, suggesting low evidence against H0.

Since the resulting P-value (0.759) is significantly larger than our chosen significance level of 0.05, we formally fail to reject the null hypothesis. The conclusion remains: we do not possess sufficient statistical evidence to assert that the true percentage of students who prefer chocolate milk differs between the two schools.

Case Study 2: One-Tailed Z-Test Implementation in Excel

In this alternative scenario, the superintendent makes a directional claim: the percentage of students who prefer chocolate milk in School 1 is less than or equal to the percentage in School 2. This sets up a one-tailed test, focusing the area of rejection entirely on one side of the distribution.

The hypotheses shift for this one-tailed test: H0: P1 ≤ P2 and Ha: P1 > P2. Note that since our sample data shows P1 (0.70) is greater than P2 (0.68), we are testing if the evidence supports the alternative hypothesis (Ha). The null hypothesis remains the status quo (P1 is not greater than P2).

The question being tested is: Based on these results, can we reject the superintendent’s claim that the percentage of students who prefer chocolate milk in school 1 is less than or equal to the percentage in school 2? Use a .05 level of significance.

The following screenshot illustrates the setup for a one-tailed two proportion z-test in Excel. The key difference compared to the two-tailed test lies solely in how the P-value is calculated in the final step:

As before, the input values for cells B1:B4 remain the same, resulting in the same pooled proportion and Z-score (0.306) in cells B6 and B7. However, the interpretation of the Z-score and the subsequent P-value calculation must account for the one-sided nature of the alternative hypothesis.

The formulas shown maintain the following structure:

• Formula in cell C6: Calculates the pooled sample proportion: p = (p₁ * n₁ + p₂ * n₂) / (n₁ + n₂).
• Formula in cell C7: Calculates the test statistic z: z = (p₁-p₂) / √p * (1-p) * [ (1/n₁) + (1/n₂)].
• Formula in cell C8: This calculates the one-tailed P-value for a right-tailed test (since P1 > P2 is our alternative). The calculation is 1 – NORM.S.DIST(B7, TRUE), which returns the area in the upper tail of the standard normal distribution corresponding to the Z-score in B7. The resulting P-value is 0.379.

Given that the P-value (0.379) is much larger than the chosen significance level of 0.05, we again fail to reject the null hypothesis. Consequently, we do not have sufficient statistical evidence to support the claim that the percentage of students preferring chocolate milk in School 1 is strictly greater than that in School 2. The superintendent’s original claim (P1 ≤ P2) cannot be rejected based on the provided sample data.