Excel Tabulation: Count Responses and Publish a Checked Summary

To tabulate responses in Excel, count how often each category occurs, then check that those counts reconcile with the source rows. A cross-tabulation adds a second category—for example, response by collection channel.

This worked example turns eight fictional responses into a checked summary you can publish without sharing the record-level worksheet. Use COUNTIFS for a fixed summary layout, or a PivotTable when you want to rearrange categories.

1. Define what one row represents

Enter this fictional dataset in A1:C9:

response_id	channel	response
R001	web	yes
R002	web	yes
R003	web	no
R004	web	not_answered
R005	phone	yes
R006	phone	no
R007	phone	no
R008	phone	not_answered

Here, one row represents one response record, and every record has exactly one channel and one response category. not_answered is an explicit category, not a zero or a “no.”

Before counting your own data:

• Check for duplicate records using the identifier and your inclusion rules.
• Standardize category labels so variants such as phone and telephone do not split one group.
• Decide how to handle missing answers; do not silently recode them as negative answers.
• Keep titles, notes and existing totals outside the source range.

Microsoft recommends column-organized data with a single header row and consistent column types for PivotTables. If your source is a presentation-style report, first reshape it into tabular data. See Microsoft’s source-data guidance.

2. Build the cross-tab with COUNTIFS

Create this summary layout in E1:I3, initially leaving the numeric cells empty:

channel	yes	no	not_answered	channel_total
web
phone

In F2, enter:

=COUNTIFS($B$2:$B$9,$E2,$C$2:$C$9,F$1)

Copy F2 across through H2, then copy F2:H2 down to F3:H3.

The formula counts rows where both conditions match: the channel in column E and the response in row 1. The dollar signs keep the source ranges fixed while allowing the summary criteria to move. Microsoft documents that COUNTIFS counts matches across multiple criteria ranges, which must have matching dimensions.

In I2, enter and copy down to I3:

=SUM(F2:H2)

For totals, enter all_channels in E4. In F4 enter =SUM(F2:F3) and copy across through I4. The result should be:

channel	yes	no	not_answered	channel_total
web	2	1	1	4
phone	1	2	1	4
all_channels	3	3	2	8

These formulas count records, not distinct people. If someone appears twice, both rows count. The ranges also stop at row 9: extend both ranges when adding records, or use Excel table structured references for a growing source.

PivotTable alternative: In Excel for Windows, select A1:C9 and choose Insert > PivotTable. Select New Worksheet, then OK. Put channel in Rows, response in Columns, and response_id in Values. Set Value Field Settings > Summarize Values By > Count explicitly. This reproduces the same counts because every example ID is populated. Microsoft documents these controls and the need to refresh after source changes.

3. Choose the percentage denominator

For web responses, the yes share is either:

• 50% of all web records: =F2/I2, or 2 ÷ 4.
• 66.7% of answered web records: =F2/SUM(F2:G2), or 2 ÷ 3.

Format the result cells as percentages; the second example is rounded to one decimal place. Both calculations are valid, but they answer different questions. Label the measure “share of all channel records” or “share of answered channel records,” rather than just “percentage.” If the chosen denominator is zero, report the share as unavailable—not 0%.

Before publishing, verify that:

• The summary total is 8, matching the eight included source records.
• Each channel totals 4, and the response totals are 3, 3 and 2.
• Shares across all three response categories sum to 100% within each channel, allowing for display rounding.
• No source categories have been omitted from the summary headers or channel rows.

Check channel totals directly against the source, too. In an unused cell, =COUNTIFS($B$2:$B$9,E2) should match I2. This counts all web records regardless of response, so a mismatch can reveal a missing response category in the cross-tab.

4. Prepare a separate public table

For a filterable dataset, use one row per channel–response combination rather than mixing detail and grand totals:

channel	response	count	channel_total	share_of_channel
web	yes	2	4	0.50
web	no	1	4	0.25
web	not_answered	1	4	0.25
phone	yes	1	4	0.25
phone	no	2	4	0.50
phone	not_answered	1	4	0.25

Document that shares are decimal fractions and that channel_total is repeated context, not a column to sum across response categories. Include the source, collection period, inclusion rules and missing-answer treatment alongside a real release.

Create a separate data-only file. Where cells contain formulas, copy their results using Home > Paste > Values; Microsoft confirms that Values pastes formula results without formulas or comments. Follow the CSV export checklist to verify the saved file.

TablePage accepts CSV, TSV, XLSX and XLS files and generates a public dataset page with a shareable link and filterable table. Upload only the reviewed release file—not the working workbook—and never upload sensitive information. For real survey data, assess disclosure risks from small groups before release; aggregation alone does not make sensitive data safe to publish. The UK Government Analysis Function’s guidance on survey tables explains how small cell counts and knowledge of participation can create identification risks.