Code open-ended survey responses in Google Sheets
Six hundred free-text answers to "why did you cancel?" do not code themselves. One formula per theme, and one for the primary theme, turn them into a table in an hour.
One column per theme
=HUNCH(E2:E900, "Does this answer give price or cost as a reason?")In Excel: =HUNCH.ASK(E2:E900, "Does this answer give price or cost as a reason?")
A yes/no column per theme lets one answer count for several themes, which is how people actually answer. Add columns for the themes your team argues about: missing feature, hard to use, poor support.
One column for the primary theme
=HUNCHPICK(E2:E900, "price|missing feature|hard to use|poor support|switched to a competitor|no longer needed|other")In Excel: =HUNCH.PICK(E2:E900, "price|missing feature|hard to use|poor support|switched to a competitor|no longer needed|other")
Real outputs
| Row | A | B | C |
|---|---|---|---|
| 1 | Answer | Price? (0–1) | Primary theme |
| 2 | Too expensive once the discount ended. | 0.98 | price |
| 3 | We switched to a competitor that has the Salesforce integration we needed. | 0.02 | switched to a competitor |
| 4 | Nobody on the team used it after the first month. | 0.03 | no longer needed |
| 5 | The reports were hard to build and support never got back to me. | 0.05 | poor support |
| 6 | We closed the project the tool was bought for. | 0.04 | no longer needed |
| 7 | Loved it, but the price doubled at renewal. | 0.92 | price |
"Loved it, but the price doubled" is coded price at 0.92 even though it starts with praise. A keyword search for "expensive" would have missed it.
The summary table
=QUERY(F2:F900, "select F, count(F) where F is not null group by F order by count(F) desc label count(F) 'answers'")Spot-check the 0.4 to 0.6 band in each yes/no column. Those are the answers the model finds ambiguous, and they are usually the interesting ones.
Questions
How do I pick the themes?
Can it handle very short answers?
Run it on your own column
Copy the template (or use the Excel add-in), paste your key, point the formula at your data. Every formula here has its Excel spelling underneath. 100 rows free to start, then $29 for 5,000 rows. Credits never expire and there is no subscription.
More guides
- Analyze open-ended survey answers in Excel Survey coding
- Categorize support tickets in a spreadsheet Ticket triage
- Tag customer reviews by topic in Google Sheets Review tagging
- AI formulas for Google Sheets: what returns a number, what returns text AI formulas compared
- Classify cold email replies in Google Sheets Cold email replies