You have a column of free-text answers and a deadline. Reading them all gives you an impression, not a result: you cannot tell a client that "many people mentioned price" and you cannot compare men and women on an impression. Analyzing open-ended responses means turning text into counts you can defend. This is the process, in the order we would do it.
The short version
- Clean the file and keep the question text with it.
- Read a sample to see what people actually say.
- Build a codebook: the list of categories you will count.
- Code every response against it.
- Review the answers that did not fit.
- Count, cross by segment, and report percentages with a few quotes.
1. Prepare the file
One row per respondent, an ID, the answer text, and the columns you will want to cross by later (age group, region, NPS group). Decide what counts as "no answer" before you start: "-", "n/a", "nothing" and blanks should not be coded as opinions. Keep the exact wording of the question next to the data. The meaning of "price" in "What should we improve?" is the opposite of its meaning in "Why did you choose us?", and whoever codes needs to know which question they are reading.
2. Read before you build anything
Read a sample, not the first 50 rows (files are often ordered by date or by interviewer). A few hundred answers, or all of them if there are fewer, picked at random. Write down every distinct idea you see, in the respondent's own words. You are looking for the vocabulary of the study, not yet for categories.
3. Build the codebook
Group the ideas into codes, and the codes into broader categories if the study needs a second level (for example "Delivery" as a category, with "Late delivery" and "Damaged on arrival" as codes). Each code needs a name a client would recognise and a one-line definition that tells a second coder where its edge is. Two decisions to make explicitly:
- One code per answer, or several? "Good price but slow delivery" carries two ideas. If you allow multiple codes, percentages add up to more than 100%, and that is correct.
- What goes in "Other"? Keep it small. If more than a tenth of the answers land in "Other", the codebook is missing a code.
For what a complete entry looks like, see our codebook example.
4. Code every response
Apply the codebook to each answer. By hand this is the slow part: a spreadsheet, a code column, and a lot of patience, and consistency drifts as the coder gets tired. With software the first pass is automatic. In Survey Coder Pro the codebook is generated from the real responses plus the question text, you can edit it before anything is coded, and rules you already know ("delivery and shipping cost are separate codes in this study") go in the question's AI instructions. The method behind this is in AI open-end coding: how it works and where it breaks.
5. Review what did not fit
This step is where analysis quality is decided. Look at the answers the coder was unsure about and the ones that could not be classified. Some are noise (gibberish, off-topic). Some are a theme you missed, and the fix is a new code, not more "Other". In Survey Coder Pro the model's low-confidence and unclassified answers are collected in a review step so you can assign them, add a code, or exclude them, instead of discovering the problem in the report.
6. Count, cross and report
Now it is a normal tabulation. Frequency per code, base clearly stated (all respondents, or only those who answered), then the codes crossed by the segments that matter. Our guide to cross-tabulation covers how to read those tables, including the multiple-response case. Report percentages with the base, show the three or four codes that matter, and add one or two verbatims per code as illustration, not as evidence.
Mistakes that cost the most
- Building categories from the first 50 answers only. The tail of the file will not fit.
- Codes that overlap. If two coders would disagree on where an answer goes, merge the codes or sharpen the definitions.
- Reporting counts without the base. "120 mentioned price" means nothing without the number who answered.
- Treating word clouds as analysis. They count words, not ideas; "not expensive" and "expensive" look the same.
- Changing the codebook halfway without recoding. Early answers are then coded under different rules than late ones.
Frequently asked questions
How do you analyze open-ended survey responses?
By coding them: define a codebook of categories from the real answers, assign each response to one or more codes, then count and cross the codes by segment. The counts are what let you report percentages instead of quotes.
How many responses do you need before coding makes sense?
There is no minimum, but below a few dozen answers a careful read and a short summary is usually enough. Coding pays off when you need percentages, comparisons between groups, or repeated waves with the same categories.
Can you analyze open-ended responses in Excel?
Yes, for small files: one column for the answer, one or more columns for codes, then a pivot table. It gets painful with multiple codes per answer and with hundreds of rows, and nothing in it keeps coding consistent for you.
What is the difference between coding and sentiment analysis?
Sentiment tells you whether an answer is positive or negative. Coding tells you what it is about. Most studies need the second; the first is useful as an extra column.
Can AI analyze open-ended survey responses?
It can do the first coding pass over every answer quickly. The decisions that need a person are the codebook and the answers the AI is unsure about. Our comparison of AI coding tools looks at how different tools handle that.
Next steps
If the answers are still being written, start with how to word open-ended questions you can code. If you already have the file, the free coding tool takes up to 100 responses, proposes a codebook from the actual text and returns an Excel, with no account needed. For the full process see the survey coding guide.