How-to
How to Make a Crosstab in Google Sheets (Pivot Table Guide)
To make a crosstab in Google Sheets, select your data, choose Insert → Pivot table, put one categorical variable in Rows and the other in Columns, then add a Values field summarized by COUNTA. Switch “Show as” to % of row or % of column for percentages.
Reviewed by the crosstabs.com methods team · Last updated
Step 1 — Insert a pivot table
Start from tidy data: one row per response, one column per variable, with headers in row 1 — for example a Region column and a Preference column. Click anywhere in the data, then choose Insert → Pivot table. Sheets detects the range automatically; accept New sheetso the pivot table doesn't overlap your data, and click Create.
An empty pivot table appears with the editor panel on the right. Everything else happens in that panel.
Step 2 — Set rows, columns, and counts
- Next to Rows, click Add and choose one variable (e.g., Region).
- Next to Columns, click Add and choose the other variable (e.g., Preference).
- Next to Values, click Add, choose either variable, and set "Summarize by" to COUNTA.
The COUNTA choice matters: the default for text fields is sometimes COUNTA already, but if you see COUNT and your responses are text (“Yes”, “No”), you'll get zeros — COUNT only counts numbers, while COUNTA counts any non-empty cell. You now have a contingency table: each cell is the number of responses with that row-column combination, with totals along the edges.
Step 3 — Show row or column percentages
Raw counts are hard to compare when group sizes differ. In the Values section of the editor, change Show as from Default to % of row or % of column(or % of grand total). Pick the direction of your grouping variable: if your groups run across the columns, use column percentages so you compare “within group A, 45% said X” against “within group B, 22% said X.”
To show counts and percentages together, click Add under Values again, select the same field, and give each copy a different “Show as” setting. For more on choosing between row and column percentages, see the crosstab reading guide.
What the pivot table can't do
A Google Sheets pivot table gives you counts and percentages — and stops there. It has no significance testing: no chi-square test, no p-value, no expected counts, no residuals, and no effect size. So it can show you that 45% of one group answered “Yes” versus 38% of another, but not whether that difference is bigger than chance would produce.
Sheets does have a CHISQ.TEST function, but using it from a pivot table is manual work: copy the counts to a plain range, build a second table of expected counts with row total × column total / grand total, and point CHISQ.TEST at both ranges. Even then you get only a p-value — no test statistic and no Cramér's V. The procedure is the same as in Excel; see how to do a chi-square test in Excel for the details.
Add the statistics in one step
If you want the pivot table andthe statistics without building expected-count formulas, export your sheet (File → Download → CSV) and drop it into our crosstab maker. Pick your two columns and you get the full table — counts, row and column percentages, expected counts, the chi-square test with p-value, and Cramér's V — in one step, free and with no signup. The file is processed in your browser and never uploaded to a server. If you already have the counts, the chi-square calculator takes them directly.
Frequently asked questions
- Why use COUNTA instead of COUNT in a Google Sheets pivot table?
- COUNT only counts numeric values, so it returns 0 if your survey responses are text like 'Yes'/'No'. COUNTA counts non-empty cells regardless of type, which is what you want when tallying categorical responses in a crosstab.
- Can Google Sheets run a chi-square test on a pivot table?
- Not directly. Pivot tables have no significance testing. Sheets does have a CHISQ.TEST(observed_range, expected_range) function, but you must first copy the pivot counts to a plain range and build the expected-counts table yourself with the formula row total × column total / grand total — the pivot table won't do it for you.
- How do I show row and column percentages at the same time?
- Add the same field to the Values section multiple times. Set one copy to 'Show as: Default' for counts, another to '% of row', and another to '% of column'. Each becomes its own value column inside the pivot table.