How to Analyze Open-Ended Survey Responses: A Step-by-Step Guide

Quick answer: to analyze open-ended survey responses, (1) clean the data, (2) read a sample to get familiar with it, (3) build a codebook of recurring topics, (4) code every response, (5) group codes into themes and count them, (6) compare themes across segments, and (7) present the themes with percentages and real quotes. You can do this by hand in a spreadsheet, with ChatGPT, or with a thematic analysis tool that groups and labels responses for you.

This guide walks through each step with a worked example, then shows how to run the same process in Excel, with ChatGPT and with an AI tool, so you can pick the method that fits your dataset. Still writing your survey? Start with these open-ended survey question examples.

What makes open-ended responses hard to analyze

Closed questions like "Rate us from 1 to 5" give you numbers you can chart straight away. Open-ended questions like "What could we improve?" give you sentences, and sentences are messy:

• People describe the same thing in different words. "Shipping takes too long", "delivery was slow" and "waited 3 weeks for my order" are one topic.
• One answer can cover several topics. "Great product, but checkout kept crashing and support never replied" touches three.
• Volume grows faster than reading time. 200 answers is an afternoon. 5,000 is a week.

The process below turns that mess into a short list of themes with counts, which is what stakeholders actually need.

Step 1: Clean and prepare the data

Export the responses to a spreadsheet with one row per respondent and keep any useful context columns, such as NPS score, plan, region or date. Then:

• Remove blank answers and non-answers such as "n/a", "no", "-" or "nothing". Count them separately so you can report them.
• Remove exact duplicates and test submissions.
• Keep each question in its own column. Never mix answers to "What do you like?" and "What should we improve?" in one analysis.
• Note your base: the number of people who gave a real answer. Every percentage you report later depends on it.

Step 2: Read a sample to get familiar with the data

Read 50 to 100 responses, or about 10 to 20 percent of a large dataset, before you write any codes. Note recurring ideas in plain words. You are not categorizing yet. You are learning how your respondents talk about the topic.

Step 3: Build a codebook

A codebook is a short list of codes, each with a definition and an example. It keeps your coding consistent, especially if more than one person is coding.

There are two ways to build one:

• Inductive (bottom-up): codes come from what you read in the sample. Best for exploratory questions like "What could we improve?"
• Deductive (top-down): you start from a fixed list, such as product areas or the stages of your customer journey. Best when you need to compare results with a previous survey.

Most teams combine both: start with the areas you care about, then add codes for anything new in the data.

Here is a codebook for the question "What could we improve?" from an online store survey:

Code	Definition	Example response
Delivery speed	Orders arriving later than expected	"Took almost three weeks to arrive"
Shipping cost	Delivery fees seen as too high	"Shipping costs more than the item"
Checkout problems	Errors or friction while paying	"Payment page froze twice"
Product quality	Defects or durability issues	"Zip broke after a week"
Customer support	Slow, unhelpful or hard-to-reach support	"Emailed twice, no answer"
Positive / nothing to improve	Praise with no improvement request	"Honestly it was all great"
Other	Relevant but too rare for its own code	"Wish you had gift wrapping"

Aim for 8 to 20 codes. Fewer and your themes are too broad to act on. More and coding becomes slow and inconsistent.

For a deeper walkthrough with include and exclude rules, nets and reliability checks, see how to code open-ended survey responses, which comes with a free Excel coding template. The choice between starting from the data or from a fixed list is covered in inductive vs deductive coding.

Step 4: Code every response

Go through each response and apply every code that fits. A single answer can get more than one code: "Delivery took ages and the box was damaged" is both Delivery speed and Product quality.

A few rules keep the results trustworthy:

• Update the codebook as you go. If a new topic appears several times, add a code and recheck earlier rows.
• Watch the Other bucket. If more than about 10 percent of responses end up in Other, you are missing a code.
• Double-code a sample. If two people are coding, have both code the same 30 to 50 responses and compare. Where you disagree, tighten the definitions.

Step 5: Group codes into themes and count them

Themes are the higher-level story your codes tell. Delivery speed and Shipping cost might roll up into a theme called Delivery. Checkout problems might sit under Website experience.

Then count. This is where most survey reports go wrong, because there are two different percentages:

• Percent of respondents: how many people mentioned the theme, divided by your base. This is the number stakeholders usually want: "34 percent of customers mentioned delivery."
• Percent of mentions: how many times the theme was coded, divided by all codes applied. This adds up to 100 percent, but because one person can mention several themes, it understates how widespread each issue is.

Report percent of respondents unless you have a specific reason not to, and always state the base, for example "n = 412 respondents who answered".

Step 6: Add context with segments

A theme count tells you what people say. Segments tell you who says it. Cross your themes with the context columns you kept in step 1:

• NPS detractors vs promoters
• New vs long-term customers
• Free vs paid plans
• Regions, product lines or survey waves

"Delivery" being mentioned by 34 percent of all respondents is useful. Learning it is mentioned by 61 percent of detractors and 9 percent of promoters tells you what to fix first.

Step 7: Present the findings

A good open-ended analysis fits on one or two slides:

1. A ranked list or bar chart of themes, sorted from most to least mentioned, showing percent of respondents and the base.
2. One or two real quotes per theme, chosen because they are typical, not because they are the most dramatic.
3. What changed, if you have a previous survey to compare with.
4. A recommendation per top theme, so the slide ends with an action rather than a number.

Avoid word clouds as your main chart. They show which words are frequent, not which problems matter, and they cannot show that "slow", "late" and "took ages" are the same issue.

More chart options, insight headlines and an example report outline are in how to present open-ended survey results.

How to analyze open-ended responses in Excel or Google Sheets

Spreadsheets work well up to a few hundred responses.

1. Put responses in column A, one per row.
2. Add one column per code from your codebook (B, C, D and so on).
3. Read each response and type 1 under every code that applies.
4. Count each code with =SUM(B2:B500).
5. Get percent of respondents with =SUM(B2:B500)/COUNTA(A2:A500).
6. Use a pivot table or COUNTIFS to split counts by segment, for example =COUNTIFS(B2:B500,1,$F$2:$F$500,"Detractor").

You can speed up the first pass with keyword formulas such as =IF(ISNUMBER(SEARCH("deliver",A2)),1,0), but treat them as suggestions only. Keywords miss synonyms ("took ages") and catch false matches ("delivered on time, great!"), so review every row.

The full spreadsheet method, including multi-keyword formulas for Excel and Google Sheets, is in how to analyze open-ended survey responses in Excel.

How to analyze open-ended responses with ChatGPT

ChatGPT and similar assistants are useful for a first look, especially for building a draft codebook. To get reliable output:

• Ask for a codebook first, with definitions, before asking it to code anything.
• Code in batches and ask for a table with the response ID and the codes applied, so you can check the work.
• Calculate counts yourself in a spreadsheet. Language models are poor at counting and may round, guess or skip rows.
• Check every quote it gives you against the original data. Models can paraphrase or invent quotes.
• Remove names, emails and other personal data before you paste anything, and check your company's AI policy.

The main drawbacks are repeatability and traceability: run the same prompt twice and you can get different themes, and it is hard to show a stakeholder which responses sit behind a number. For copy-paste prompts and a safer workflow, see how to analyze survey responses with ChatGPT.

How to analyze open-ended responses with a thematic analysis tool

Dedicated tools automate steps 3 to 5. In Apercu, for example, the process is:

1. Upload your CSV or Excel export and confirm the suggested text column.
2. Optionally describe the data, for example "Answers to 'What could we improve?' from online store customers", which helps the AI choose clearer labels.
3. Choose how broad or detailed the themes should be.
4. Apercu groups responses that talk about the same thing, then uses AI to name each group.
5. Review each theme's count, percentage and example responses. Rename themes or move responses that landed in the wrong group.
6. Export the results, or on paid plans generate a PDF report with a summary, key findings and recommendations.

One difference to know: Apercu assigns each response to the single theme it fits best, while manual coding can give one response several codes. For most feedback surveys that trade-off is worth the time saved. For academic research that needs multi-coding, use the tool for a first pass and code by hand from there.

Which method should you use?

	Manual or Excel	ChatGPT	Qualitative software (NVivo, ATLAS.ti)	AI thematic analysis tool
Best for	Under 300 responses	Quick exploration	Academic and interview research	Hundreds to 100,000 responses
Time for 500 responses	Roughly 4 to 8 hours	About an hour, plus checking	Several hours	Minutes, plus review
Multiple codes per response	Yes	Yes	Yes	One theme per response
Accurate counts	Yes	Must calculate yourself	Yes	Yes
Repeatable	Depends on the coder	Low	High	High
Skills needed	Patience	Prompting and checking	Training	None

Common mistakes to avoid

• Reporting percent of mentions as if it were percent of people. It makes every theme look smaller than it is.
• Not stating the base. "40 percent" means little without "of 412 respondents who answered".
• Cherry-picking quotes. Pick quotes that represent the theme, not the angriest one.
• Too many codes. Fifty codes is a list, not an insight. Merge until each theme supports a decision.
• Analyzing two questions together. Answers to different questions mean different things, even when they use the same words.
• Ignoring the non-answers. If 30 percent of people wrote "nothing", that is a finding too.

FAQ

How do you code open-ended survey responses?

Read a sample of responses, write a codebook with a short definition and example for each code, then go through every response and apply all the codes that fit. Count how many respondents received each code and group related codes into themes.

How many open-ended responses do you need before analysis is worth it?

There is no minimum. Even 30 responses can reveal clear themes. Manual coding stays practical up to a few hundred responses, while larger datasets are where spreadsheets and AI tools save the most time.

Should I report percent of respondents or percent of mentions?

Usually percent of respondents, because it tells you how many people raised an issue. Percent of mentions adds up to 100 percent but understates each theme when people mention several topics.

Can ChatGPT analyze open-ended survey responses?

Yes, for a first pass. It is good at drafting a codebook and summarizing themes, but you should calculate counts yourself, verify quotes against the original data and remove personal information before pasting responses.

How do you present open-ended survey results?

Show a ranked bar chart of themes with percent of respondents and the base, add one or two representative quotes per theme, highlight differences between key segments, and end with a recommendation for each top theme.

How long does it take to analyze open-ended survey responses?

By hand, expect roughly 4 to 8 hours for 300 to 500 responses, including building the codebook. AI thematic analysis tools reduce the grouping and labeling to minutes, leaving time for reviewing and presenting the results.