Microsoft Forms Data Analysis from an Excel Export

Microsoft Forms data analysis turns an exported response workbook into counts and numeric summaries by department, period, and response category. Keep the response rows intact, separate missing scores from valid answers, and reconcile every summary to the export before comparing groups.

Start with a dated response export

Microsoft documents how to view form results and download a workbook. Each response occupies a row and each question a column; a downloaded copy has no live connection to the form. Record the export date and the latest included completion time beside your working copy.

Choose a completed reporting period. A partial month may contain a different mix of departments than a closed month. If the form changes, record which question version applies to each period rather than treating revised wording as the same measure.

Keep the response ID for tracing and duplicate checks. Retain only the fields needed for the business question in the analysis copy. Use a supplied department answer or an approved mapping; an anonymous response does not supply a department simply because another workbook contains employee details.

For general workbook preparation, see data analysis in Excel. This workflow concentrates on the exported response rows.

Prepare the fields before summarizing

Suppose a US distributor asks whether its order handoff has improved and requests an optional numeric score on a scale from 1 through 5. These are illustrative response rows, with dates displayed in US format. The last score cell is empty, not the text "blank."

Place those fields in A1:E9. Check that column B contains actual date values and E contains numbers or empty cells. Normalize department spellings, check IDs for duplicates, and verify that every numeric score is within the supplied scale. Resolve exceptions before producing the summary.

Add Month in F1 and Numeric answer in G1. In F2 and G2, fill down:

=DATE(YEAR(B2),MONTH(B2),1)
=IF(ISNUMBER(E2),1,0)

The second formula counts numeric answers after the scale check. It would also count an out-of-range number if you skipped that check. The business spreadsheet data quality guide covers the wider preparation work.

Count responses and scores on separate bases

Create a summary with Month in H, Department in I, Responses in J, Valid scores in K, Score total in L, and Mean score in M. Enter the relevant month-start dates and department names in H2:I5. These formulas in row 2 summarize the matching response rows:

=COUNTIFS($F$2:$F$9,H2,$C$2:$C$9,I2)
=SUMIFS($G$2:$G$9,$F$2:$F$9,H2,$C$2:$C$9,I2)
=SUMIFS($E$2:$E$9,$F$2:$F$9,H2,$C$2:$C$9,I2)
=IF(K2=0,"",L2/K2)

Microsoft's COUNTIFS documentation explains that a row is counted when all supplied criteria match. Keep each criteria range the same size.

Means are rounded to two decimal places for display. September Operations has two responses but only one numeric answer: its score is 3 / 1 = 3.00. Dividing by both submissions would incorrectly treat the unanswered score as zero.

Reconcile the summary and compare categories

The response counts sum to 8, the valid-score counts to 7, and the score totals to 26. The overall mean must therefore be 26 / 7 = 3.71, using unrounded values. In the summary, calculate it with:

=SUM(L2:L5)/SUM(K2:K5)

Do not average the displayed department means. That would give equal weight to groups with different numbers of valid answers.

For category counts, put Improved in a summary criterion cell such as P2, then count against column D:

=COUNTIFS($D$2:$D$9,P2)

Each share divides its count by 8. This is a single-choice question, so category counts must equal responses. With multiple-choice questions, selections can exceed respondents; label that denominator explicitly.

Sales has a higher mean score in September than August, but these small response groups establish a descriptive difference only. Check who responded, question consistency, and missing answers before calling it an improvement across the department. For a broader survey workbook, use survey analysis in Excel.

Where it goes wrong

• Counting rows as people. Response IDs identify submissions; repeat submissions may require an approved retention rule.
• Using submissions as the score denominator. Optional questions need their own valid-answer counts.
• Combining revised questions. Similar labels do not establish a consistent scale or question meaning.
• Calling categories sentiment. A supplied choice such as Improved is a response category; free-text comments need a separate, documented interpretation.
• Reporting participation without invitations. Response counts alone cannot show the share of eligible people who answered.

Covirage is not affiliated with Microsoft.

Compare your response export in Covirage

Upload a sample of your own exported response workbook to Covirage. Covirage maps the columns, and its tools compute and check response counts and supplied numeric summaries; the external AI model explains the comparisons with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for Excel analysis for the supporting file-based workflow.