Crosstabs in Excel: Build a Cross-Tabulation With a PivotTable

A crosstab (a cross-tabulation or contingency table) counts how many cases fall into each combination of two categorical variables, such as treatment group by outcome. In Excel the tool for this is a PivotTable: you place one variable in Rows, the other in Columns, and a field in Values that Excel counts. This guide gives the PivotTable steps as Microsoft documents them, then goes one step further than most how-tos and shows how to test the finished table for association with the CHISQ.TEST function, including the expected-count formula and the five-per-cell caution.

What crosstabs are used for in research

A crosstab is the usual first look at whether two categorical variables are related: survey answer by respondent group, exposure by outcome, pass or fail by site. It is also the input to the chi-square test of independence and to association measures built on it. If you are working in another package, the same table is produced by crosstabs in R and by the Crosstabs procedure described in the guide to reading SPSS output.

Install or access (official source only)

PivotTables are a feature of Excel itself; there is nothing extra to install. Microsoft documents the feature on its support site under Create a PivotTable to analyze worksheet data. That page also describes Insert > Recommended PivotTable, which Microsoft lists as available to Microsoft 365 subscribers only; the manual route below works without it. The test in the later section uses the CHISQ.TEST worksheet function, documented in Microsoft’s CHISQ.TEST article.

Step-by-step: build a crosstab with a PivotTable

The source data must have a single header row, with one row per case and one column per variable. Microsoft states this requirement for PivotTable source data.

1. Click any cell in the data range.
2. Choose Insert > PivotTable.
3. In the dialog, confirm the table or range Excel proposes. There is an optional checkbox, Add this data to the Data Model, which a plain crosstab does not need.
4. Choose where to put the PivotTable, New Worksheet or Existing Worksheet, then select OK.
5. In the PivotTable Fields pane, drag the first categorical variable (for example group) into the Rows area and the second (for example outcome) into the Columns area. Microsoft notes that by default Excel places non-numeric fields in Rows, date and time fields in Columns and numeric fields in Values, and that you can drag a field between areas at any time.
6. Drag a field into the Values area so that the cells hold counts. A case identifier column works, or the same categorical field you used for Rows.

The counting trap: Sum versus Count

Microsoft states: “By default, PivotTable fields placed in the Values area are displayed as a SUM.” If Excel reads the field as text, it counts instead. So a text field in Values gives case counts, while a numeric field, including a category that you coded as 1, 2, 3, is added up, which produces meaningless totals in a crosstab. If the cells hold sums where you expect counts, use the arrow beside the field name in the Values area, choose Value Field Settings, and under Summarize Values By pick Count (menu path as documented for Excel on Windows). Microsoft also advises against mixing data types within one column.

Output and how to read it

Each cell of the PivotTable is the number of cases with that row category and that column category. To compare groups of different sizes, read the table as percentages of each row rather than raw counts: divide each cell by its row total. Copy the counts into ordinary cells beside the PivotTable and build the row totals with SUM, so the percentage formulas stay simple and do not depend on the PivotTable layout.

A crosstab shows pattern, not significance. Whether the pattern is larger than chance would produce is a separate question, answered by the test below. Once you have a test result, standardized residuals show which cells drive it, and that guide already covers the Excel calculation, so it is not repeated here.

Testing the table with CHISQ.TEST

The syntax is CHISQ.TEST(actual_range, expected_range), and both arguments are required. The actual range is your table of observed counts. The expected range is a second table of the same shape, where, in Microsoft’s description, each cell is the ratio of its row total times its column total to the grand total:

expected = (row total × column total) / grand total

Worked example (illustrative counts)

The counts below are invented to show the layout; they are not data from a study. Suppose 100 cases, 50 per group, with outcome Improved, No change or Worse:

Group	Improved	No change	Worse	Row total
Treatment	30	15	5	50
Control	20	20	10	50
Column total	50	35	15	100

1. Put the headers in row 1, the counts in B2:D3, row totals in E2:E3, column totals in B4:D4 and the grand total in E4, each built with SUM.
2. In B7 enter =$E2*B$4/$E$4 and fill it across to D7 and down to row 8. The expected counts are 25, 17.5 and 7.5 in each row.
3. In any empty cell enter =CHISQ.TEST(B2:D3,B7:D8).

For these counts the function returns about 0.112. The degrees of freedom are (rows − 1) × (columns − 1) = (2 − 1) × (3 − 1) = 2, the value Microsoft documents for a multi-row, multi-column table. The chi-square statistic itself is 4.38, which you can confirm by summing (observed − expected)² / expected over the six cells. With this p-value the difference between the groups would not be called significant at the conventional 0.05 level. See the chi-square test guide for how to report and interpret it.

What the function returns, and the five-per-cell caution

The function returns the probability: the chance of seeing a chi-square statistic at least this large if the two variables were independent. It returns that probability, not the statistic. Microsoft also cautions that the test is reliable only when expected values are not small, and notes that some statisticians suggest at least 5 per cell. In the example, the smallest expected count is 7.5, so the condition holds. Check your own expected table, not the observed one, against that threshold.

Troubleshooting

• Cells show large totals instead of counts. The Values field is numeric and is being summed. Change it with Value Field Settings > Summarize Values By, or use a text field.
• The PivotTable dialog proposes the wrong range or the field names look wrong. Check that the source has a single header row.
• CHISQ.TEST returns #N/A. Microsoft documents #N/A when the actual and expected ranges have different numbers of data points, and also when the table has a single row and a single column (1 × 1).
• Expected counts below 5. Microsoft’s caution applies: the test is less reliable when expected values are small.

Free alternatives

Excel is convenient for a quick table, but dedicated statistics software builds the crosstab and the test in one step. The table lists tools with a CASRAI guide that covers their crosstab or contingency-table route.

Tool	How it produces a crosstab	CASRAI guide
R	Four routes: table(), xtabs(), CrossTable() in gmodels and tabyl() in janitor	Crosstabs in R
JASP	Contingency tables are among its common statistical designs, alongside t tests, ANOVA and regression	JASP guide
SPSS	Crosstabs procedure, which prints a Case Processing Summary before the results	Reading SPSS output

For a task-by-task comparison of no-cost packages, see free statistical software for research.

FAQ

Why does my Excel PivotTable show sums instead of counts?

Because the field in the Values area is numeric. Microsoft states that fields in Values are summed by default, and that Excel counts instead when it reads a field as text. Switch the summary to Count in Value Field Settings.

How many observations per cell do I need for CHISQ.TEST?

Microsoft says the test needs expected values that are not small and notes that some statisticians suggest at least 5 per cell. The check applies to expected counts, which you calculate as row total times column total divided by the grand total.

Does CHISQ.TEST give me the chi-square statistic?

No. It returns the probability that a chi-square statistic that high could occur by chance if the variables were independent. To report the statistic, sum (observed − expected)² / expected over all cells, using the expected table you built for the test.

Sources (official documentation)

• Microsoft Support, Create a PivotTable to analyze worksheet data, checked 2026-10-29.
• Microsoft Support, CHISQ.TEST function, checked 2026-10-29.