How to Perform a Chi-Square Test in Excel

Key Insights

• Chi-square tests determine whether observed categorical data differs significantly from expected distributions, making them essential for A/B testing, survey analysis, and quality control
• Excel provides both manual calculation methods and built-in functions like CHISQ.TEST() that return p-values directly, letting you choose between understanding the mechanics or getting quick results
• The test requires expected frequencies of at least 5 per cell for valid results—violating this assumption leads to unreliable conclusions that can derail your analysis

Introduction to Chi-Square Tests

Chi-square tests answer a simple question: is the pattern in your categorical data real, or could it have happened by chance? Unlike t-tests or ANOVA that compare means, chi-square tests compare frequencies—how often things occur across different categories.

You’ll encounter two main types. The goodness of fit test checks whether your observed data matches an expected distribution. For example, does your website traffic follow the expected 40/30/30 split across three landing pages? The test of independence examines whether two categorical variables are related. Do purchasing decisions depend on customer age group?

This article focuses on the test of independence since it’s more common in practical applications. You’ll learn to perform it manually (so you understand what’s happening) and with Excel’s built-in functions (for efficiency).

Preparing Your Data in Excel

Chi-square tests require data in contingency table format—a grid where rows represent one categorical variable and columns represent another. Each cell contains the count of observations falling into that combination.

Here’s a properly structured example. Suppose you surveyed 300 customers about their preferred product tier across three age groups:

Set this up in Excel with clear labels. Place your observed frequencies in cells B2:D4, row totals in column E, column totals in row 5, and the grand total in E5. Consistent structure matters because your formulas will reference these positions.

Critical requirements for valid chi-square analysis:

• Each observation belongs to exactly one cell
• Categories are mutually exclusive
• Expected frequencies (calculated next) should be at least 5 per cell
• Data represents counts, not percentages or means

Calculating Expected Frequencies

Expected frequencies represent what you’d observe if the two variables were completely independent—if age had no relationship to product preference. The formula is straightforward:

Expected = (Row Total × Column Total) / Grand Total

For the 18-34 age group and Basic tier: Expected = (100 × 100) / 300 = 33.33

Create a parallel table for expected values. In cell B9 (assuming your expected table starts in row 9), enter:

=(B$5*$E2)/$E$5

This formula uses mixed references strategically. B$5 locks the row to always reference column totals. $E2 locks the column to always reference row totals. $E$5 is fully locked to the grand total. Copy this formula across all cells in your expected frequency table, and the references adjust correctly.

Your expected frequency table should look like this:

Verify your work: each row and column in the expected table should sum to the same totals as your observed data. If they don’t match, you have a formula error.

Computing the Chi-Square Statistic Manually

The chi-square statistic measures the overall discrepancy between observed and expected frequencies. For each cell, you calculate how far the observed value deviates from expected, square it (to eliminate negative values), and divide by expected (to normalize). Then sum everything.

χ² = Σ [(O - E)² / E]

You can calculate this cell by cell. Create a third table for the chi-square contribution of each cell:

=((B2-B9)^2)/B9

This gives you the contribution from the 18-34/Basic cell. Copy across all cells, then sum the entire table for your chi-square statistic.

A more elegant approach uses a single array formula. Name your observed range (B2:D4) as observed and your expected range (B9:D11) as expected. Then use:

=SUMPRODUCT((observed-expected)^2/expected)

This calculates the difference, squares it, divides by expected, and sums—all in one formula. For our example data, the chi-square statistic equals approximately 10.42.

Understanding the manual calculation helps you troubleshoot issues and explain results to stakeholders. You can see which cells contribute most to the overall statistic, identifying where the strongest deviations occur.

Using Excel’s Built-in CHISQ Functions

Excel provides three primary chi-square functions that streamline analysis:

CHISQ.TEST() returns the p-value directly, comparing observed and expected ranges:

=CHISQ.TEST(B2:D4, B9:D11)

This single formula replaces the entire manual calculation process. It returns the probability of observing a chi-square statistic this large (or larger) if the variables were truly independent.

CHISQ.DIST() returns the cumulative probability for a given chi-square value:

=CHISQ.DIST(10.42, 4, TRUE)

The arguments are: chi-square value, degrees of freedom, and cumulative (TRUE for cumulative distribution). This returns the probability of getting a value less than or equal to 10.42.

CHISQ.DIST.RT() returns the right-tail probability—what you actually need for hypothesis testing:

=CHISQ.DIST.RT(10.42, 4)

This equals 1 minus the cumulative distribution and matches what CHISQ.TEST() returns.

CHISQ.INV() and CHISQ.INV.RT() work in reverse, giving you the chi-square value for a specified probability:

=CHISQ.INV.RT(0.05, 4)

This returns 9.49—the critical value at α = 0.05 with 4 degrees of freedom. Any chi-square statistic above this threshold is statistically significant.

Interpreting Results and Statistical Significance

Degrees of freedom determine which chi-square distribution applies to your test:

df = (number of rows - 1) × (number of columns - 1)

For our 3×3 table: df = (3-1) × (3-1) = 4

Compare your p-value to your chosen significance level (typically α = 0.05):

• p-value < 0.05: Reject the null hypothesis. The variables are likely related.
• p-value ≥ 0.05: Fail to reject the null hypothesis. No significant relationship detected.

For our example, =CHISQ.TEST(B2:D4, B9:D11) returns approximately 0.034. Since 0.034 < 0.05, we conclude that product tier preference is significantly associated with age group.

Alternatively, compare your chi-square statistic to the critical value:

=IF(10.42 > CHISQ.INV.RT(0.05, 4), "Significant", "Not Significant")

Since 10.42 > 9.49, the result is significant.

Important caveats: statistical significance doesn’t imply practical importance. A large sample can make trivial differences significant. Always examine the actual frequencies alongside your p-value.

Practical Example: A/B Testing Application

Let’s apply chi-square testing to a real scenario. You’re testing two landing page designs and want to know if conversion rates differ significantly.

The data:

• Version A: 1,247 visitors, 89 conversions
• Version B: 1,312 visitors, 127 conversions

Set up your observed frequency table in A1:C3:

Calculate expected frequencies in A6:C8:

Cell B6: =(B$8*$D2)/$D$8

This gives:

• Version A Converted: (216 × 1247) / 2559 = 105.26
• Version A Not Converted: (2343 × 1247) / 2559 = 1141.74
• Version B Converted: (216 × 1312) / 2559 = 110.74
• Version B Not Converted: (2343 × 1312) / 2559 = 1201.26

Now run the test:

Cell B10: =CHISQ.TEST(B2:C3, B6:C7)

Result: approximately 0.021

Calculate degrees of freedom:

Cell B11: =(ROWS(B2:C3)-1)*(COLUMNS(B2:C3)-1)

Result: 1

Build a summary section:

Cell A13: Chi-Square Statistic
Cell B13: =SUMPRODUCT((B2:C3-B6:C7)^2/B6:C7)

Cell A14: Degrees of Freedom
Cell B14: 1

Cell A15: P-Value
Cell B15: =CHISQ.TEST(B2:C3, B6:C7)

Cell A16: Critical Value (α=0.05)
Cell B16: =CHISQ.INV.RT(0.05, B14)

Cell A17: Conclusion
Cell B17: =IF(B15<0.05, "Significant difference", "No significant difference")

The p-value of 0.021 is less than 0.05, so you conclude that Version B’s higher conversion rate (9.68% vs 7.14%) represents a real difference, not random variation. You can confidently recommend implementing Version B.

This template works for any A/B test involving categorical outcomes. Swap in your numbers, and the formulas handle the rest. Save it as a template for future tests, and you’ll have reliable statistical analysis at your fingertips without external tools.