Someone asks how your survey results differ by gender, age or region, and the data is sitting in a spreadsheet. Building a crosstab in Excel is the obvious first move, and for a few tables it works well. This guide shows how to do it two ways, with a PivotTable and with formulas, on a small worked example, then covers weighting and the points where Excel stops being the right tool. For the concepts behind the table, see our guide to what a crosstab is, with banners, bases and nets.
Key takeaways
- A crosstab in Excel is a two-way table of counts or percentages, built with a PivotTable or with COUNTIFS formulas.
- Start with one row per respondent and one column per question, with a header row and no merged cells.
- Compare groups with column percentages, not counts, and keep the base (the number of respondents) next to them.
- For weighted data, sum a weight column with SUMIFS, or Sum of Weight in a PivotTable, instead of counting rows.
- Excel copes with a few one-off tables; multiple-response questions, nets, significance tests and repeat waves are where it breaks.
How should survey data be laid out for a crosstab in Excel?
Put one row per respondent and one column per question, with a single header row. Give every respondent an ID. Avoid merged cells, blank header cells and subtotal rows inside the data, because both PivotTables and formulas treat every row as a record.
Here is the illustrative dataset used throughout: 12 respondents, their gender, their preferred plan (Basic, Plus or Pro) and a weight used later. The numbers are made up and kept small so the arithmetic is visible. It sits in columns A to D:
| ID | Gender | Plan | Weight |
|---|---|---|---|
| R01 | Female | Pro | 1.5 |
| R02 | Male | Basic | 0.75 |
| R03 | Male | Plus | 0.75 |
| R04 | Female | Basic | 1.5 |
| R05 | Male | Basic | 0.75 |
| R06 | Male | Pro | 0.75 |
| R07 | Female | Plus | 1.5 |
| R08 | Male | Plus | 0.75 |
| R09 | Male | Basic | 0.75 |
| R10 | Female | Pro | 1.5 |
| R11 | Male | Basic | 0.75 |
| R12 | Male | Plus | 0.75 |
Select any cell and press Ctrl+T to format the range as an Excel table. New rows then flow into everything built on it.
How do you make a crosstab in Excel with a PivotTable?
- Click any cell in the data and choose Insert > PivotTable. If a menu opens, pick From Table/Range. Choose New Worksheet and click OK.
- In the PivotTable Fields pane, drag Plan to the Rows area and Gender to the Columns area.
- Drag ID to the Values area. Text IDs appear as Count of ID. If your IDs are numbers, Excel sums them instead, so click the field in Values, choose Value Field Settings and select Count.
- Right-click any number in the table and choose Show Values As > % of Column Total.
- Drag ID to Values a second time and leave it as a count. That second field shows the base for each column, which the percentages alone hide.
When new responses arrive, click inside the PivotTable and choose PivotTable Analyze > Refresh.
What does the worked example look like?
After step 3, the PivotTable gives this count table:
| Plan | Female | Male | Total |
|---|---|---|---|
| Basic | 1 | 4 | 5 |
| Plus | 1 | 3 | 4 |
| Pro | 2 | 1 | 3 |
| Base | 4 | 8 | 12 |
Read across the Pro row and the counts suggest a small gap: 2 women against 1 man. But there are twice as many men in the sample, so counts cannot be compared across columns. Step 4 divides each cell by its column total:
| Plan | Female | Male | Total |
|---|---|---|---|
| Basic | 25.0% | 50.0% | 41.7% |
| Plus | 25.0% | 37.5% | 33.3% |
| Pro | 50.0% | 12.5% | 25.0% |
| Base | 4 | 8 | 12 |
The arithmetic: women choosing Pro, 2 ÷ 4 = 50.0%. Men choosing Pro, 1 ÷ 8 = 12.5%. Men choosing Basic, 4 ÷ 8 = 50.0%. Total choosing Basic, 5 ÷ 12 = 41.7%. Each column adds to 100%. Now the pattern is clear: in this sample, women lean to Pro and men to Basic.
With a base of 4 women, that is an illustration, not a finding. A real study needs far larger bases and a significance test before anyone repeats it.
Can you build a crosstab in Excel with COUNTIFS formulas?
Yes. Formulas suit a fixed layout that must look the same every time, such as a table feeding a chart. Type the plan names in F2:F4, the genders in G1:H1, and put this in G2: =COUNTIFS($B$2:$B$13,G$1,$C$2:$C$13,$F2). Fill it across and down. The mixed references keep the gender from row 1 and the plan from column F as the formula moves.
Add a base row with =SUM(G2:G4) in G5, then build the percentage table with =G2/G$5. Unlike a PivotTable, formulas never reshuffle when the data changes. The cost is that every new banner or question means editing ranges by hand.
How do you do the same in Google Sheets?
Select the data and choose Insert > Pivot table, then pick a new sheet. In the pivot table editor, add Plan under Rows, Gender under Columns and ID under Values, summarized by COUNTA. Set Show as to % of column for column percentages. COUNTIFS and SUMIFS work in Google Sheets with the same syntax as in Excel.
How do you do cross tabulation in Excel with weighted data?
Weighting corrects a sample that over- or under-represents a group. Suppose the customer base is half women and half men, but the sample has 4 women and 8 men. Each woman gets a weight of 6 ÷ 4 = 1.5 and each man 6 ÷ 8 = 0.75, so both groups sum to 6.
To weight a crosstab, add up weights instead of counting rows. With formulas, swap COUNTIFS for =SUMIFS($D$2:$D$13,$B$2:$B$13,G$1,$C$2:$C$13,$F2). In a PivotTable, replace Count of ID in Values with Sum of Weight, then apply % of Column Total as before.
| Plan | Female (weighted) | Male (weighted) | Total (weighted) | Total % |
|---|---|---|---|---|
| Basic | 1 × 1.5 = 1.5 | 4 × 0.75 = 3.0 | 4.5 | 4.5 ÷ 12 = 37.5% |
| Plus | 1 × 1.5 = 1.5 | 3 × 0.75 = 2.25 | 3.75 | 3.75 ÷ 12 = 31.25% |
| Pro | 2 × 1.5 = 3.0 | 1 × 0.75 = 0.75 | 3.75 | 3.75 ÷ 12 = 31.25% |
| Weighted base | 6.0 | 6.0 | 12.0 | 100% |
The Female and Male column percentages do not change, because everyone in a column has the same weight. The Total column does: Pro rises from 25.0% unweighted to 31.25% weighted, because women, who favor Pro, now count for half the total. Keep the unweighted bases of 4 and 8 on the table too: significance tests need them, and a weighted 6 women does not mean you interviewed six.
Where do Excel crosstabs break for survey data?
The steps above handle one single-answer question by one banner variable. Survey work quickly asks for more, and the manual effort and risk of error grow with it.
| Task | Excel approach | What goes wrong |
|---|---|---|
| Multiple-response questions stored in several columns | One COUNTIFS per option column, divided by respondents who answered | Easy to divide by answers instead of people, or to include people who never saw the question |
| Different bases per question | A filter or extra criterion on each table | One table without the filter and its percentages use the wrong base |
| Nets such as “Plus or Pro” | Group items in the PivotTable or add a helper column | The net is rebuilt in each table and drifts from one to the next |
| Significance testing | Copy counts out and write z-test formulas | No built-in test; formulas break when the layout changes |
| Many banner points at once (gender, age, region side by side) | Nest fields in Columns, or one PivotTable per banner variable | Nesting multiplies columns; separate tables must be lined up by hand |
| Every question, every wave | Copy the workbook and repoint ranges | Repetition grows with every table, and ranges miss new rows |
| Version control | Workbooks saved and emailed with new file names | Two versions of the same number end up in different decks |
Our comparison of crosstabs and pivot tables explains why a general-purpose summary tool struggles here.
When is Excel enough, and when should you move on?
Excel is enough for a few one-off tables on clean, single-answer questions, where nobody needs significance letters and the analysis will not be repeated. It is also a good way to learn, because you see every count and division.
Move on when the table becomes a deliverable: dozens of questions, several banners, weights, nets and a tracker that comes back every quarter. That is the job crosstab software is built for. In mTab, Halo Reports produces crosstabs and banner tables with weighting and significance testing, alongside charts and interactive report pages. Our guide to survey crosstabs shows how to structure the full set of tables for a study.
What are the common mistakes?
- Counting blanks. A respondent who skipped the question appears as “(blank)” in a PivotTable and inflates the base. Clear it in the Row Labels filter, or add a “not blank” criterion to COUNTIFS (
"<>"), so the base is only people who answered. - Percentages of the wrong total. % of Row Total or % of Grand Total answers a different question. To compare groups in columns, use % of Column Total.
- Reporting unweighted results from a weighted study. If the study has a weight column, counting IDs misstates the population. Use Sum of Weight or SUMIFS.
- Leaving filters on. A field in the Filters area, or a slicer, set for an earlier question stays applied when you copy the PivotTable. Check the filter area before every table.
What should you do next?
Take one question from your own survey, build a crosstab in Excel with a PivotTable, then rebuild it with COUNTIFS and check the two match. If the numbers agree and the work took minutes, Excel is serving you well. If you need the same table across many questions, banners and waves, look at one governed platform for wide, complex studies and global brand trackers built for exactly that.