How to Analyze Open-Ended Survey Responses in Excel and Google Sheets

Quick answer: to analyze open-ended survey responses in Excel, put one response per row, clean the text, add one column per code, mark each response with a 1 under every code that applies, then count each code with SUM and split by segment with COUNTIFS. Keyword formulas can speed up the first pass, but every row still needs a human check.

Download: the free open-ended survey coding template has the codebook, coding grid and summary formulas from this guide already set up. It opens in Excel and Google Sheets.

When Excel is the right tool

Excel and Google Sheets work well when you have up to a few hundred responses, one or two open-ended questions and time to read every answer. Beyond about 500 responses, manual coding gets slow and inconsistent, and an AI thematic analysis tool saves most of the time.

Step 1: Set up the sheet

Export your survey to CSV or Excel and keep only what you need:

Column	Content
A	Response ID
B	The open-ended answer
C	A segment, such as NPS group, plan or region
D onward	One column per code

Step 2: Clean the responses

Use a helper column to tidy each answer:

=TRIM(CLEAN(B2))

Then:

• Filter for blanks and non-answers ("n/a", "no", "nothing", "-") and move them to a separate sheet. Count them, because "40 people had nothing to add" is a finding.
• Remove duplicates with Data > Remove Duplicates (Excel) or Data > Data cleanup > Remove duplicates (Google Sheets).
• Count your base, the number of valid answers left: =COUNTA(B2:B1000).

Step 3: Build a codebook

Read 50 to 100 answers and write a short list of codes with a definition for each. Our guide on how to code open-ended survey responses explains how to write definitions that two people would apply the same way. Put each code name in the header row from column D onward.

Step 4: Speed up the first pass with keyword flags

Keyword formulas can pre-fill likely codes, which you then confirm by reading.

Excel, one keyword:

=IF(ISNUMBER(SEARCH("deliver",$B2)),1,"")

Excel, several keywords for one code:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"deliver","shipping","arriv","late"},$B2)))>0,1,"")

Google Sheets, several keywords with a regular expression:

=IF(REGEXMATCH(LOWER($B2),"deliver|shipping|arriv|late"),1,"")

SEARCH is not case-sensitive, so "Delivery" and "delivery" both match.

Treat these flags as suggestions only. Keywords miss synonyms ("took ages", "still waiting") and catch false matches ("delivered on time, love it"). Paste the formulas as values, then read every row and correct the codes.

Step 5: Code every response

Go row by row and put a 1 under every code that applies. One response can have several codes. Leave other cells empty.

Add a check column that counts codes per row:

=SUM(D2:J2)

Filter it for 0 to find responses you have not coded yet.

Step 6: Count the themes

On a summary sheet, list your codes in column A and add:

Measure	Formula (for the code in column D)
Respondents mentioning	=SUM(Coding!D2:D1000)
% of respondents	=B2/COUNTA(Coding!B2:B1000)
Detractors mentioning	=COUNTIFS(Coding!D2:D1000,1,Coding!C2:C1000,"Detractor")
% of detractors	=D2/COUNTIF(Coding!C2:C1000,"Detractor")

Percent of respondents can add up to more than 100 percent because one person can mention several topics. That is correct, and it is the number stakeholders usually want.

Step 7: Compare segments with a pivot table

For a quick segment view, select the coding grid and insert a pivot table:

• Rows: your segment column
• Values: the sum of each code column

This shows, for example, that "Delivery speed" is concentrated among detractors.

Step 8: Chart the results

Use a horizontal bar chart sorted from most to least mentioned, with percent of respondents on the axis and the base in the title. Avoid pie charts, because percentages over 100 percent do not fit a pie, and avoid word clouds as the main chart because they show words, not problems. More options are in how to present open-ended survey results.

Optional: a quick word-frequency check

A word count can reveal topics you missed in the codebook. In Microsoft 365, TEXTSPLIT can split answers into words, and a pivot table on the resulting list counts them. Use it only to find candidate codes, never as the analysis, because frequent words are not the same as frequent problems.

Limits of the spreadsheet method

• Time: expect roughly 4 to 8 hours for 300 to 500 responses.
• Consistency: coding drifts over a long session, and two people code differently without strict definitions.
• Repeatability: next quarter you start again from scratch.

If those limits bite, upload the same file to Apercu. It groups the responses into named themes with counts and examples in minutes, and you can export the results back to Excel on paid plans.

FAQ

Can Excel automatically categorize open-ended responses?

Not reliably on its own. Keyword formulas with SEARCH or REGEXMATCH can pre-fill likely codes, but they miss synonyms and create false matches, so every row needs checking. AI thematic analysis tools categorize by grouping similar responses instead of matching keywords.

How do I count how many responses mention a theme in Excel?

If each code has its own column with a 1 for responses that mention it, use =SUM(D2:D1000). To count within a segment, use COUNTIFS, for example =COUNTIFS(D2:D1000,1,C2:C1000,"Detractor").

Is Google Sheets good for analyzing open-ended responses?

Yes, for the same dataset sizes as Excel. REGEXMATCH makes multi-keyword flags easier than in Excel, and sharing makes it simpler for two people to code and compare.

What is the best chart for open-ended survey results?

A horizontal bar chart of themes sorted by percent of respondents, with the base (n) shown. Add a stacked or grouped bar when you compare segments.