How to code open-ended survey responses in Excel
Open-ended survey answers are where the insight hides — and where analysis stalls. Thematic coding turns free text into countable themes without pretending every nuance fits a multiple-choice box. You do not need expensive CAQDAS software for a few hundred responses; you need a transparent codebook and discipline.
What thematic coding is (and is not)
Coding assigns each answer (or each idea inside an answer) to one or more themes. It is not automatic “sentiment only,” and it is not dumping answers into a word cloud and calling it research. Good coding is reproducible: another person using your codebook should land nearby.
A lean Excel workflow
- Put each answer on its own row with an ID, question ID, and raw text.
- Read 20–30 answers and jot candidate themes on paper. Merge overlaps early.
- Write a codebook: theme name, definition, inclusion/exclusion examples, example quotes.
- Code in a column (primary theme). Add a second column if multi-label is required.
- After 50 rows, revisit the codebook. Rename or split themes that are muddy.
- Pivot counts by theme; pull illustrative quotes for the report.
Speed up discovery with a preview tool
Before you invent themes from scratch, paste answers into the free survey theme preview. It is keyword-based — not a final codebook — but it surfaces clusters like pricing, performance, support, and mobile so you are not staring at a blank sheet.
Rules that keep codes honest
- Prefer concrete themes (“Mobile performance”) over vague ones (“Negative”).
- Separate topic from sentiment if both matter.
- Document when an answer is “Other / unclear” instead of forcing a fit.
- If two coders disagree often, the codebook — not the coder — needs work.
When volume exceeds an afternoon
A few dozen answers are DIY. Several hundred with dual coding and a client-facing summary is a project. Our Upwork Catalog service returns Excel themes with a clear codebook on a fixed timeline so stakeholders can read the pivot, not a wall of text.
Bottom line
Preview to explore, codebook to decide, Excel to count, quotes to persuade. Keep the process boring and your findings get trusted.
Example: turning mess into a codebook
Suppose 120 answers to “What frustrated you?” Mentions of lag, crash, and load time collapse into Performance. Mentions of price, plan, and worth collapse into Pricing. Mentions of ticket reply time become Support. Ambiguous one-word answers (“meh”) go to Unclear. After coding, a pivot shows Performance 34%, Pricing 22%, Support 18%. You now know where the product narrative should focus — and you have quotes ready for the slide appendix.
Single-label vs multi-label
Single-label is easier to pivot and present. Multi-label is more faithful when respondents pack three ideas into one sentence. If you multi-label, also store a “primary” theme for charting so executives are not confused by percentages that sum past 100% without explanation.
Inter-rater spot checks
Have a second person code 20 random rows with the codebook closed from your filled sheet. Where you disagree, refine definitions. This hour prevents a week of stakeholder pushback later.
Reporting without overclaiming
Themes are structured evidence, not a census of all customers. Say “among respondents who answered this open question” rather than “users think.” Pair counts with two or three short quotes per top theme. Offer the coded Excel as an appendix for skeptics.
When the dataset is large or the deadline is fixed, a Catalog coding delivery returns themes plus codebook so you can facilitate the workshop instead of still labeling rows the night before.
From coded sheet to stakeholder story
Executives remember one chart and three quotes. Build the chart from primary theme counts, then pick quotes that are short, specific, and non-outlier. Avoid the funniest rant if it misrepresents the distribution. Link back to the coded Excel so analysts can verify.
If leadership asks for “AI summary only,” still keep the coded sheet. Summaries without traceable rows do not survive procurement or academic-adjacent scrutiny.
Preparing the raw export
Export CSV from Typeform, Google Forms, SurveyMonkey, or your product analytics tool. Keep Respondent ID if available. Remove obviously empty rows. If one respondent answered multiple open questions, either stack them with a Question column or keep wide format with one coding column per question — do not mix both styles mid-project.
Run a quick pass in the free theme preview to see whether your draft themes cover the mass of answers. If 40% fall into Other, you need more theme granularity before full coding.
Time estimates
Experienced coders often average 40–80 short answers per hour once the codebook is stable, slower on the first 50. Dual coding doubles time but buys trust. Budget explicitly; unpaid “quick coding” is how quality collapses on Friday nights.