Build observed and expected counts for a customer-category comparison in Excel. Reproduce the chi-square statistic, interpret its p-value, and keep business importance separate from statistical evidence.
Chi square data analysis in Excel compares observed category counts with the counts expected if two classifications were independent. Build both tables, then use CHISQ.TEST to obtain a p-value. Keep the original counts and group shares beside the result so the reader can see the actual business difference.
For example, a US business services company may ask whether preferred support channel differs between Standard and Premium customers. This is a question about categorical responses, not sales or cost amounts.
Start with an export containing one response per sampled customer: customer reference, service tier, and preferred channel. Each customer selects either Phone or Email. Resolve duplicate responses before counting, and use the same survey period for both tiers.
The Excel data analysis workflow covers source-table preparation. For this test, the important extra requirement is that a customer contributes to exactly one cell. Repeated contacts from the same customer would give frequent callers more influence and undermine independence.
Suppose the cleaned export produces these counts. Put the table in A1:D4, with observed counts in B2:C3.
| Tier | Phone | Row total | |
|---|---|---|---|
| Standard | 30 | 20 | 50 |
| Premium | 10 | 40 | 50 |
| Column total | 40 | 60 | 100 |
Use =SUM(B2:C2) for the Standard row total and fill down. Column totals use =SUM(B2:B3) and =SUM(C2:C3). The grand total must match the cleaned export's eligible customer count.
For repeatable preparation, retain the category-count table rather than manually entering percentages. The practice-data guide explains how to retain control totals when testing a workbook layout.
The null hypothesis is that preferred support channel and service tier are independent. Under that assumption, each tier has the same channel proportions as the full table.
Expected count = Row total × Column total / Grand total
NIST's contingency-table guidance explains this independence comparison and the chi-square calculation. Here, Phone accounts for 40 / 100 = 40% of responses and Email for 60 / 100 = 60%. Each tier contains 50 customers, so its expected counts are 50 × 40 / 100 = 20 and 50 × 60 / 100 = 30.
Place the expected table in F1:H3, with tier labels in F and expected counts in G2:H3:
| Tier | Expected Phone | Expected Email |
|---|---|---|
| Standard | 20 | 30 |
| Premium | 20 | 30 |
In G2 enter:
=$D2*B$4/$D$4
Fill right to H2 and down through row 3. The locked references preserve the observed table's row totals, column totals, and grand total.
Check that expected row totals equal 50 and 50, expected column totals equal 40 and 60, and the expected grand total equals 100. If any check fails, fix the formulas before running the test.
For each cell, calculate its contribution to the statistic:
Cell contribution = (Observed count − Expected count)² / Expected count
| Cell | Observed | Expected | Contribution |
|---|---|---|---|
| Standard, Phone | 30 | 20 | 5.0000 |
| Standard, Email | 20 | 30 | 3.3333 |
| Premium, Phone | 10 | 20 | 5.0000 |
| Premium, Email | 40 | 30 | 3.3333 |
| Total chi-square statistic | 16.6667 |
Contributions and the total are displayed to four decimals; calculate with unrounded values. In J2 enter =(B2-G2)^2/G2, fill across to K2 and down through row 3, then total:
=SUM(J2:K3)
For the p-value, use the original observed and expected ranges, excluding labels and totals:
=CHISQ.TEST(B2:C3,G2:H3)
Microsoft documents CHISQ.TEST as a test for independence based on these ranges. For this table, the p-value is approximately 0.00004456. The degrees of freedom are (2 − 1) × (2 − 1) = 1.
Set the significance threshold before viewing results. At a chosen threshold of 0.05, this p-value supports rejecting independence, subject to the test's assumptions. It is not the probability that the null hypothesis is true.
Standard customers selected Phone in 30 / 50 = 60% of responses. Premium customers selected it in 10 / 50 = 20%. The difference is 40 percentage points.
That gap is easier for a support leader to act on than the statistic alone. It suggests checking how service tier, onboarding, customer tenure, and survey participation relate to channel preference. It does not prove that changing a customer's tier changes their preferred channel.
Include the observed counts, group shares, sampling scope, and caveats in the Excel analysis report. If the question concerns joint movement of numeric sales and cost observations, use covariance analysis instead.
Covirage is not affiliated with Microsoft.
Bring the customer-category workbook to Covirage to inspect the counts and shares behind your comparison. Covirage maps the columns; its tools compute and check business summaries, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See Excel analysis with Covirage for the workbook workflow.
No. It returns the p-value for the test using the observed and expected ranges. Calculate the statistic separately by adding the squared observed-minus-expected differences divided by their expected counts. Label both outputs so a report cannot confuse them.
Use original category counts for the test. Percentages alone discard the number of observations behind each group. You can show percentages beside the test to explain business importance, provided each percentage uses the correct group denominator.
It indicates that the observed differences would be unusual under the independence assumption and the test's conditions. It does not establish why groups differ, how large the business impact is, or whether the source records represent all customers.