Guides · For teachers, schools and researchers

How to analyse open-ended survey answers in Google Sheets

Tick-box questions give you instant charts. Questions where people write their own answers give you the interesting stuff, and a wall of text. Here's how to turn those written answers into clear themes, counts and a chart in Google Sheets.

About an 8-minute read · Works with any Google Form, or any column of text

This works for student voice surveys, course feedback, exit tickets, staff surveys, parent questionnaires and research questionnaires: anything where people answer in their own words.

1

Get your answers into a sheet

In your Google Form, open the Responses tab and click Link to Sheets (or View in Sheets if it's already linked). Each question becomes a column and each person a row. If your answers came from somewhere else, paste them into a column with one answer per row.

Before you start, make a copy of the sheet (File › Make a copy) so the original responses stay untouched.

2

Decide on your codes

A code is a short label for an idea that keeps coming up, such as Homework load or Lunch and food. Read the first 20 or 30 answers and note the ideas you see repeated. Turn them into 5 to 10 codes to start with. You can always add more.

Write your codes in a list on a new tab called "Codes". Keep the names short and use the same wording every time.

3

Tag every answer

Add a new column next to the answers called "Codes". Read each answer and type the code or codes that fit, separated by commas when an answer covers two ideas.

Year groupWhat one thing would make school better?Codes
Year 7The lunch queue is so long I only get ten minutes to eat.Lunch and food
Year 9Less homework on the same night, sometimes I get four pieces at once.Homework load
Year 10I feel stressed about GCSEs and don't know who to talk to.Wellbeing and stress

To avoid typos, you can turn the column into a dropdown: select it, choose Data › Data validation, add a rule, pick Dropdown (from a range) and choose your list on the Codes tab. A standard dropdown holds one choice per cell, so if lots of answers need two codes, typing them is simpler. Just copy the names exactly from your list.

Tip: some answers won't fit any code ("not sure", "nothing"). Give them a code like "No suggestion" so you can count them too, rather than leaving gaps.

4

Count them with formulas

On your Codes tab, put each code in column A. In column B, count how many answers mention it. If your codes are in column E of a tab called "Form responses 1", use:

=COUNTIF('Form responses 1'!E:E, "*"&A2&"*")

The stars mean "anywhere in the cell", so an answer tagged "Homework load, Wellbeing and stress" counts towards both codes. To show it as a share of everyone who answered, divide by the number of answers:

=B2/COUNTA('Form responses 1'!D2:D)

Format that column as a percentage, then select both columns and choose Insert › Chart. A bar chart, sorted from biggest to smallest, is usually clearest.

5

Compare groups

Often the useful finding is who said what: Year 7 worry about lunch, Year 10 about stress. Add a column for each group and count with two conditions. With year group in column B:

=COUNTIFS('Form responses 1'!B:B, "Year 7", 'Form responses 1'!E:E, "*"&A2&"*")

Copy it across for each group. Remember that groups can be different sizes, so compare percentages, not raw counts.

6

Report what you found

For each of your top codes, give the count, the percentage, and one or two real quotes that show what people meant. Quotes are what make a summary land with colleagues or governors: "17% asked for more clubs" is fine, but the student who said "a football club for girls" is what people remember.

Got hundreds of answers?

Themely for Sheets does the slow parts for you. It finds the column of written answers, shows them one at a time so you can tag each with a click (or tag every answer that mentions a word at once), writes the codes into your sheet, and builds the summary table, example quotes, chart and group comparison for you.

See Themely for Sheets

Free for up to 50 answers per spreadsheet.

Questions people ask

How many answers do I need to read before choosing codes?

Usually 20 to 30 is enough to see the main ideas. You'll still add a code or two as you go, which is fine. Just recheck the earlier answers when you do.

Can I just search for keywords instead?

Searching helps you find candidates quickly, but people say the same thing in different words ("homework", "too many assignments", "work every night"). Always read the answer before you tag it.

Should one person do all the tagging?

It's more consistent if one person tags everything, or if two people agree the code list first and check a sample of each other's work.

More guides