Analysing survey results in Excel when half the answers are words.

Most survey tools export one row per respondent and one column per question, which is the shape everything else needs. Some export one row per answer instead, and that file has to be reshaped before any counting starts, or every percentage computed on it is a percentage of answers rather than of people.

Check the row count against the number of respondents the survey tool reports. A file with three times as many rows as there were respondents is in the long shape, and every figure computed on it directly will be wrong in a way that looks plausible.

"Strongly agree" is text. Averaging a column of it produces an error at best and, in tools that coerce silently, a number computed on the few cells that happened to be numeric.

Map the labels to numbers once, in one place, and apply the same mapping to every question using the scale. The common failure is two questions on the same five-point scale mapped in opposite directions, which makes them cancel where they should corroborate.

Reverse-worded items then need recoding — a five on "I would not recommend this" is a one, not a five — and it is worth checking afterwards that the recoded item still moves with its neighbours, because respondents frequently answer it as though it were worded positively.

A tick-all-that-apply question usually exports as one cell holding "Email; Phone; Post". Split it into one column per option, each holding a yes or a no, before counting anything.

Then state the base. Forty per cent of respondents and forty per cent of selections are different claims that get reported identically, and the honest denominator is respondents.

The written answers are the half of the survey most analyses throw away, because a column of sentences fits no formula. Code them into themes — a dozen is usually enough — and read each theme against the scores of the people who wrote it.

A theme appearing in forty comments means something different depending on whether those forty people rated you two or five, and that pairing is the most useful output the survey has.

Everything above except the theme names is mechanical: reshaping the grid, mapping labels to numbers, recoding reversed items, unpacking select-all cells. It is exactly the work an automated pass does before analysis, which is why uploading the raw export beats cleaning it by hand first.

The judgment that cannot be automated away is who answered. Before quoting any comparison between groups, check completion by segment — a difference that tracks who abandoned the survey is measuring dropout, not opinion.