How to Run a Chi-Square Test in Excel With CHISQ.TEST

A chi-square test in Excel takes four steps: count your data, build a table of expected counts, run =CHISQ.TEST(observed, expected), and read the p-value it returns. The test tells you whether two categories, such as marketing channel and "bought or not", look related or could just be chance. This guide is for analysts, students and anyone handling survey or campaign data who wants the formulas without a statistics package.

What a chi-square test tells you#

A chi-square test of independence compares the counts you observed with the counts you would expect if the two categories had nothing to do with each other. If the gap between them is large, the categories are probably related.

The example in this guide uses 300 customers who came from three channels. Each one either bought (Yes) or didn't (No). The question: does the purchase rate depend on the channel?

The test gives a p-value: the probability of seeing a gap at least this large if there were really no relationship. You compare it with a cut-off you choose before you look, usually 0.05.

• p below 0.05: the data give evidence of a relationship at the 5% level.
• p at or above 0.05: there isn't enough evidence of a relationship. That is not proof that there is none.

Step 1: Count the observed data with COUNTIFS#

Start with a table of counts, one row per channel and one column per outcome. If your raw data has one row per customer, with the channel in column A and Yes/No in column B of a sheet called Data, COUNTIFS builds the table for you. In B2:

=COUNTIFS(Data!$A$2:$A$301,$A2,Data!$B$2:$B$301,B$1)

Fill it across and down. $A2 always reads the channel name in column A, and B$1 always reads the outcome in row 1. The guide to COUNTIFS and SUMIFS and the COUNTIFS function guide explain the syntax, and a pivot table count gives the same numbers if you prefer clicking.

Add totals with SUM. The observed table looks like this:

Overall, 100 of 300 customers bought: one in three. Email converted 40%, Social 25% and Search 35%. The test checks whether those differences are bigger than chance would produce.

Step 2: Build the expected table with mixed references#

The expected count for each cell is what you'd see if every channel converted at the overall rate:

Expected = row total × column total ÷ grand total

For Email / Yes that is 100 × 100 ÷ 300 = 33.33. Put an "Expected" table below the first one, in rows 8 to 11, and type this in B9:

=$D2*B$5/$D$5

The dollar signs make the formula safe to fill across B9:C11:

• $D2 locks the column, so it always reads a row total from column D, but the row changes as you fill down.
• B$5 locks the row, so it always reads a column total from row 5, but the column changes as you fill right.
• $D$5 locks both, so every cell divides by the grand total.

Press F4 (Mac: ⌘T) while the cursor is in a reference to cycle through the $ options. The guide to the dollar sign in Excel covers mixed references in detail. Every expected count here is 33.33 for Yes and 66.67 for No, because each channel has 100 customers.

Step 3: Get the p-value with CHISQ.TEST#

CHISQ.TEST takes the observed range and the expected range, in that order:

=CHISQ.TEST(B2:C4,B9:C11)

The result is about 0.072. Don't include the totals row or column: the ranges must hold only the counts, and both must be the same size.

CHISQ.TEST works out the degrees of freedom (df) for you. For a table with more than one row and more than one column, df = (rows − 1) × (columns − 1). Here that is (3 − 1) × (2 − 1) = 2.

CHISQ.TEST needs Excel 2010 or later, including Microsoft 365 and Excel for Mac. Older files may use CHITEST, which gives the same answer. The Analysis ToolPak has no chi-square tool, so formulas are the standard way to do this in Excel.

Step 4: Calculate the chi-square statistic#

Many reports ask for the statistic as well as the p-value. It adds up (observed − expected)² ÷ expected for every cell:

=SUMPRODUCT((B2:C4-B9:C11)^2/B9:C11)

The result is 5.25. Compare it with the critical value for your cut-off and df:

=CHISQ.INV.RT(0.05,2)

That returns about 5.99. Since 5.25 is below 5.99, the result isn't significant at the 5% level, which matches the p-value of 0.072. You can also go the other way: =CHISQ.DIST.RT(5.25,2) returns the same p-value.

To see which cells drive the result, look at each cell's share of the 5.25:

Social / Yes contributes most: Social converted fewer customers than expected (25 instead of 33.33).

How to interpret the result honestly#

A p-value of 0.072 means: if channel and purchase were unrelated, a gap at least this big would turn up about 7% of the time. At the 0.05 cut-off, that's not enough evidence to say the channels convert differently.

Keep these points in mind when you report it:

• Not significant doesn't mean "no difference". With 100 customers per channel, a real but modest difference can easily miss the cut-off. More data might change the result.
• Significant doesn't mean "big" or "causes". A tiny p-value says a relationship probably exists, not how strong it is or why. Report the percentages next to it, as in the guide to percentage of total.
• Pick the cut-off before you look. Changing from 0.05 to 0.10 after seeing 0.072 is not a fair test.

Common chi-square mistakes in Excel#

These are the slips that give wrong p-values:

1. Using percentages instead of counts. The test needs raw counts. 40% of 100 and 40% of 10,000 are very different evidence.
2. Including totals in the ranges. Use B2:C4, not B2:D5.
3. Expected counts below 5. A common rule of thumb is that every expected count should be at least 5. If some are smaller, combine categories or collect more data.
4. Counting people twice. Each person or item must be in exactly one cell. Survey questions where people can tick several answers break this rule; see how to analyse survey results for counting each answer.
5. Forgetting the dollar signs. Without $D2*B$5/$D$5, the expected formula points at the wrong totals once you fill it.
6. Comparing with other software on a 2×2 table. Excel's CHISQ.TEST doesn't apply a continuity correction. Some statistics tools apply one by default for 2×2 tables, so their p-value can be slightly larger.

Practise it#

The statistics part is two formulas. The skills that make it work are counting with conditions and locking references. Practise COUNTIFS and SUMIFS in a live spreadsheet to build count tables quickly, then practise relative and absolute references so formulas like $D2*B$5/$D$5 fill correctly the first time. Try the exercise on this page to check the expected-count formula now.