How to analyse survey results

To analyse survey results, export all responses to a spreadsheet, keep the raw file untouched, clean a working copy, count each answer, convert counts into percentages of the people who answered that question, cross tab the questions that matter by one segment, and code open comments into a handful of themes. Pivot tables do most of the counting. The judgement is in choosing the base for each percentage and refusing to over read small groups.

Export first, and know the shape of the file

Before any formula, open the export and read the header row. Most form tools write one row per response and one column per question, but they differ in how they write questions with several parts. A rating grid, an address or a multiple choice question can arrive as one column holding several values, or as several columns. That decides every step that follows.

Take the course evaluation form from a fictional training institute. It asks which of four classes the student sat in, then three rating grids: the teaching, the materials and the outcome. The outcome grid rates three tasks as not yet, with help or confidently. Exported as a single column, one student’s outcome cell might read like this:

I can do bank reconciliations: Confidently | I can prepare a BAS: With help | I can set up a chart of accounts: Not yet

Save the raw export as the first sheet and never touch it again. Do all cleaning on a copy, so any figure can be traced back to what was actually submitted.

Clean before you count

Cleaning is dull and decides whether the numbers are right.

Remove tests. Delete responses submitted before the survey went live, identified by date and time.

Split joined columns. Use Text to Columns in Excel or SPLIT in Google Sheets on the separator, here the pipe, to give each statement its own column. Then strip the statement text so only the answer remains, and rename the column with a short label such as Outcome BAS.

Trim and standardise free text. Remove leading spaces. Leave spelling in comments alone unless it blocks coding.

Decide what blanks mean. A skipped optional question is blank, not zero. The materials grid in the course example has a Did not use column; treat it as its own answer, never as a low score. That rule is what stops an unopened online portal from dragging down the materials average.

Counts and percentages

Build a summary sheet with one block per question. For a single choice question, list each answer label and use COUNTIF against the answer column, or build a pivot table with the question in rows and a count in values. Microsoft and Google both document pivot tables for exactly this job: summarising many rows by category.

Then choose the base. The percentage is the count of an answer divided by the number of people who answered that question, not the total number of responses. Suppose 58 students submitted, all answered the required outcome grid, and 21 rated preparing a BAS as not yet. That is 21 divided by 58, or 36%. If an optional question was answered by only 40, its percentages use 40.

Write the base beside every result: “36% (21 of 58)”. Readers then see immediately when a striking percentage rests on a small number.

For rating scales, map labels to numbers in a small lookup table, such as not yet as 1, with help as 2 and confidently as 3, and average with AVERAGEIF or a pivot table. Report the share choosing the top answer as well, because an average can hide a split group.

Cross tabs: one question against one segment

A cross tab answers “does this differ by group?”. Put the segment in pivot table columns, the answer in rows, and show values as a percentage of the column total. In the course example the segment is the class, never the trainer, so the coordinator can see whether Saturday morning students feel more confident on BAS than Thursday evening students without the survey naming a colleague.

Keep cross tabs to the questions that feed a decision. Crossing every question with every segment produces hundreds of small tables and a few differences that are pure chance. Survey researchers such as those at Pew Research Center publish their methods partly because the number of comparisons and the size of each group decide how much a difference can be trusted.

Two rules keep cross tabs honest:

• hide or merge any group with fewer than five responses
• treat differences between groups under about thirty as directional, not proven

The employee pulse survey builds the first rule into the form itself: its team question says a team under five responses is never reported alone.

Open answers

Open comments are where the reasons live, and they are the easiest part to misreport. Work in three passes.

1. Read every comment once, without tagging.
2. Write five to eight themes that cover most of what you read, plus Other.
3. Read again, tag each comment with one or more themes, and count tags with a pivot table.

Quote sparingly and only where the comment cannot identify its writer. The course example asks for “one thing to change before next term” as a required question, which gives the coordinator one actionable sentence per student rather than a page of general feelings.

For a score followed by a reason, as in the customer feedback survey, tag the reasons separately for high and low scores. The themes behind a 9 and the themes behind a 3 are rarely the same list.

The steps in one table

The table at the end of this article lists the ten steps from export to report, with the formula or method for each and the mistake that most often creeps in at that step. Keep it beside the workbook the first time you analyse a survey, and note any step you skipped in the report itself.

Common mistakes

Dividing by the wrong base. Using total responses for an optional question makes every answer look rarer than it is.

Averaging labels that are not a scale. “Did not use” is not a 0.

Over reading small groups. A difference between a group of six and a group of nine is usually noise.

Mixing survey versions. If a question changed halfway through collection, analyse the two periods separately.

Reporting everything. Lead with the three findings that change a decision. The rest belongs in an appendix. For the questions themselves, the guide on how to write survey questions shows how wording choices made at the start make this analysis easier, and the note on margin of error explains why small samples need wide caution.

For NPS, the NPS survey example sets out the calculation from its own responses table.

Build it

The CSV export from a form here writes one column per input field, with Submitted At as the first column, and it pages through every response rather than only the page on screen. Composite fields stay in one column: a rating grid is written as each statement and its answer separated by pipes, an address as its parts joined by commas, and a table as a JSON array of the filled rows. Choice fields are written as their option labels, not stored values, and dates use the field’s own format. Partial responses are not stored, so every row is a submitted response.

The page on exporting form responses to CSV shows what each field type looks like in the file, and the tutorial on viewing, exporting and managing responses covers the responses table and the export button.