UX and research · Intermediate · 16 min
Code open-ended survey answers into themes
By Padding · Updated 18 Nov 2026
Google Sheets or Excel · November 2026
You will have around 200 free-text survey answers coded against a written codebook that you and a colleague tested for agreement, and a theme table with counts and example quotes.
On this page
A survey question like "What almost stopped you placing your first order?" brings back a column of sentences nobody can chart. Reading them all and writing down what "people seemed to say" feels like analysis, but it is mostly memory: the vivid answers win, and the dull, common ones disappear. Coding fixes that. You give each answer one or more short labels from a fixed list, then count the labels.
The list of labels is the codebook. It matters more than the coding itself, because two people reading the same answer will label it differently unless each label has a written definition and a rule for where its edges are. This tutorial spends most of its time there: you draft codes, write them down properly, code the same sample as a colleague, measure how often you agree, and tighten the definitions until you do. Only then do you code the rest.
The example is Tidewell, a fictional meal-kit delivery service. Its post-purchase survey asked new customers "What, if anything, almost stopped you placing your first order?" and got 212 answers. Use your own export in its place; the steps are the same for any single open question.
Step 1 · Set up one row per answer
Open a new spreadsheet and make a tab called Answers. Paste the export so that each answer sits on its own row, with these columns:
- A: ID. A stable number for each answer (1, 2, 3 ...). Keep the respondent ID from your survey tool here if it has one, so you can join answers back to other questions later.
- B: Answer. The text exactly as written. Do not fix spelling or shorten it; you will quote from this column.
- C: Code 1, D: Code 2, E: Code 3. Empty for now. Most answers need one code, some need two, a few need three. If you find yourself wanting a fourth, the answer probably needs splitting into two rows or your codes are too narrow.
- F: Notes. For anything you want to come back to.
Delete rows where the answer is empty. Keep rows that say "n/a", "nothing" or "no": these are answers, and "nothing almost stopped me" is often one of the largest groups. Note the final row count at the top of the Notes column; this is your base.
Finally, add a column G: Shuffle with =RAND() in every row, then copy the column and paste it back as values so it stops changing. Sorting by this column gives you a random order, which you need in the next two steps so that the first answers you read are not just the earliest respondents.
Check it worked
Your Answers tab has one answer per row, an ID in every row, no blank answers, and a fixed random number in column G. The row count is written down. Sort by column G and back by column A: the IDs return to their original order.
Step 2 · Read a random batch and draft codes
Sort by Shuffle and read the first 40 answers without labelling anything. Then read them again, and for each one write a short phrase in Notes describing what the person is talking about, not how they feel about it. "Worried about the price of the first box" is useful; "negative" is not.
After 40 answers, copy all your phrases to a new tab called Draft codes and group the ones that mean the same thing. For Tidewell, the groups looked like this:
- First-box price or the discount looking too good to be true
- No delivery slot that suited them
- Not sure portions would be big enough
- Could not find allergen or diet information
- Having to create an account before seeing the menu
- Not knowing the brand
- Nothing stopped them
Give each group a short code name in lower case with hyphens, such as price-first-box or delivery-slot. Short names are faster to type and harder to misread than sentences. Aim for somewhere between 6 and 15 codes. Fewer than that and each code hides several different problems; many more and you and your colleague will never agree.
Add two codes every codebook needs: other for a clear answer that fits no code yet, and no-answer for text that does not answer the question ("good", "see above", "asdf").
Check it worked
Your Draft codes tab lists between 8 and 17 code names (your topic codes plus other and no-answer). Every one of the 40 answers you read could be given at least one of them. If more than four of the 40 would land in other, read those again and add a code.
Step 3 · Write the codebook
A code name on its own is not enough. Make a tab called Codebook with one row per code and these columns:
- Code. The short name.
- Definition. One sentence on what the answer must be about.
- Use when. The signals that put an answer in this code.
- Do not use when. The nearest neighbour it gets confused with, and how to tell them apart.
- Example. One real answer from your data, copied word for word.
The "Do not use when" column is where agreement is won. For Tidewell, price-first-box and trust-brand overlapped: "the deal seemed too cheap to be real" is about price and about trust. The team wrote:
price-first-box. Definition: the answer is about what the first order costs or whether the discount is worth it. Do not use when: the person doubts the company is genuine or will deliver at all; usetrust-brandfor that, even if price is mentioned as the reason for the doubt.
Write one rule like that for every pair of codes you can imagine being confused. Also write three general rules at the top of the tab:
- Code what the person said, not what you think they meant.
- Give an answer every code it clearly fits, up to three; do not force a single code.
- When torn between two codes, choose the one whose "Use when" matches the words in the answer, and write the doubt in Notes.
Check it worked
Every row in Codebook has all five columns filled. Every example is a real answer you can find in column B with your spreadsheet's find function. Each code has at least one "Do not use when" line naming another code.
Step 4 · Code a sample separately with a colleague
Pick 40 answers you have not used yet: sort by Shuffle and take rows 41 to 80. Copy their ID and Answer columns into two new tabs, Sample A (yours) and Sample B (your colleague's). Each tab gets its own Code 1 to Code 3 columns.
Turn the code columns into dropdowns so nobody can type delivery-slots in one place and delivery-slot in another. In Google Sheets, select the code cells, open Data, then Data validation, add a rule with the dropdown criterion, and point it at the Code column of your Codebook tab. Excel calls the same feature a drop-down list, under Data Validation on the Data tab.
Give your colleague the codebook and 15 minutes to read it before they start. Then code separately, without talking about individual answers. If either of you meets an answer the codebook does not cover, use other and write why in Notes. Do not edit the codebook mid-sample; the point is to test the version you both started with.
Check it worked
Sample A and Sample B each have 40 rows with the same IDs in the same order, every row has at least a Code 1, and every code cell holds a value from the dropdown. Neither of you has seen the other's tab.
Step 5 · Measure agreement and tighten the codebook
Make a tab called Compare with the 40 IDs and answers, then pull in both coders' first codes side by side. In column E, mark whether they match:
=IF(C2=D2, 1, 0)Here C is your Code 1 and D is your colleague's. The average of column E is your percentage agreement on the main code. Comparing only Code 1 keeps this simple; if multi-code answers are common in your data, also compare whether each of you used the same set of codes and note the difference.
Percentage agreement is easy to read but flatters you when one code dominates, because two people would agree often by chance alone. Cohen's kappa corrects for that; most statistics packages and several free online calculators compute it from your two columns. Use it if you will report agreement to people who will ask about it. For a working team, a raw figure plus a look at where you disagree is usually enough to act on.
Now filter column E to show the zeros, and go through each disagreement together. For each one, decide which of these caused it:
- A definition was vague. Rewrite it and add the answer as an example.
- Two codes overlap. Add a "Do not use when" rule, or merge them.
- A code was missing. Add it. Check whether answers in
otherbelong there. - One of you misread the answer. No codebook change; note it.
Agree on a target before you look at the result, such as 8 in every 10 answers matching, so the number does not get explained away afterwards. If you fall short, code a fresh 20 answers (rows 81 to 100) separately with the revised codebook and compare again.
Check it worked
Compare shows a percentage agreement figure for the first sample, and, if you ran a second round, for that one too. Your Codebook has a dated note at the top listing what changed after each round. Every disagreement row has a reason written next to it.
Step 6 · Code the rest and watch for drift
With the codebook settled, split the remaining answers between you, or code them all yourself. Paste the agreed codes from the sample rounds back into the Answers tab first, so nothing is coded twice.
Coding 150 or so short answers takes most people one to two hours. Work in sittings of about 40 answers and take a break between them: after long runs people stop reading carefully and drift towards a few familiar codes.
Two habits keep the result consistent:
- New code, then backtrack. If you add a code halfway through, search the rows you have already coded for the same idea and recode them. Write the new code in the codebook with a definition and example before you use it.
- Spot-check each other. When you have both finished, each of you codes 10 random rows from the other's share without looking at their codes. If you disagree on more than two or three, sit down and resolve them before counting.
Keep an eye on other. If it grows past a handful of answers, read them together: there is usually a code hiding inside.
Check it worked
Every row in Answers has a Code 1. Every code in columns C to E appears in the Codebook tab. Filtering column C for other shows only a few rows, each with a note explaining why it did not fit.
Step 7 · Count the codes and build the theme table
Make a tab called Themes. List every code in column A. In column B, count how many answers used it, wherever it appears in Code 1 to Code 3:
=COUNTIF(Answers!$C$2:$E$213, A2)Change 213 to your last row. Because each answer gets a code at most once, this count is the number of respondents who mentioned it. In column C, divide by the number of answers that are not no-answer to get a share, and format it as a percentage. Say which base you used whenever you show the figure; "38 of 196 people who answered" is clearer than "19%".
Then step back from codes to themes. A theme is a group of codes that tell one story. Tidewell grouped price-first-box and trust-brand into a theme called "Doubts about whether the offer is real", and account-before-menu and diet-info-missing into "Could not see enough before committing". Add a column D for the theme each code belongs to, and sum the counts per theme, remembering that one person may appear in two codes of the same theme; check those rows if the total looks high.
Finish the tab with one row per theme and these columns: theme, codes it includes, number of people, share, and two or three quotes. Pick quotes that show the range inside the theme, not only the most quotable one, and keep them word for word with the answer ID next to each.
Check it worked
Your Themes tab shows each code with a count, each theme with a total, the base number used for shares, and at least two quotes per theme, each with an ID you can find in Answers. The code counts add up to at least the number of usable answers (more, if some answers have two or three codes).
Next steps
Your theme table says what people mentioned and how often. Before you present it, read what the counts can and cannot tell you in Read survey results without fooling yourself, especially if the people who answered the open question differ from those who skipped it.
To turn the themes into statements a team can act on, work through Turn research themes into insights. If you want to collect cleaner answers next time, Design a short survey people finish covers how wording and position change what an open question brings back. For interview notes rather than survey text, Build an affinity map from interview notes uses a looser, bottom-up grouping that suits longer material.
Part of these paths
Related tutorials
- Design a short survey people will finishYou will have a survey that takes under five minutes, built from a short list of questions that each feed a decision, piloted with three people, sent, and reported with an honest completion rate.
- Turn research themes into insight statementsYou will have five insight statements, each saying what people do, why they do it and what it means for the design, with quotes from several participants and a confidence level beside it.
Need this done for you?
Padding does this work for growing companies, from the first diagnosis to a system that keeps working.
Get new tutorials