Use Gemini in Google Sheets to sort survey comments into themes

What This Does

After a program ends you are left with a column of open-text survey comments and no time to read them all closely. Google Sheets has an AI function that can categorize the contents of a cell. You define the themes and it tags each comment. A PivotTable then counts them.

Before You Start

• Google's support page says the AI function requires an eligible Google Workspace or Google AI plan, and it can be blocked by admin settings or language settings. If a cell shows "AI function not available," ask your Workspace admin.
• Your comments are in one column of a Google Sheet, one comment per row, with a learner code instead of a name.
• You have decided on four to six themes that matter to you, such as Content relevance, Pacing, Facilitator, Logistics, Manager support, and Other.

Steps

1. Prepare the comments

Paste the comments into a fresh sheet or tab. Remove names, and remove any comment that describes a specific person by role or situation. Put headers in row 1.

2. Add an AI column

Google's page describes a function you can type into a cell, either =AI() or =Gemini(), or add from the menu through Insert, then Function, then AI. It also describes an Insert AI column option at the top right of a table. The syntax is a prompt in quotes, then an optional range the prompt refers to. Use the range so the model sees the comment. Google recommends this because the function does not see the rest of your spreadsheet.

In the first row next to the first comment, enter something like:

=AI("Assign this survey comment to exactly one theme: Content relevance, Pacing, Facilitator, Logistics, Manager support, or Other. Reply with the theme name only.", B2)

3. Generate and fill down

Select the cell with the function and click Generate and Insert. Then fill the formula down the column. Google's page notes that only the first 350 selected AI cells generate at one time, so work in batches for a large set. It also says you cannot undo the function, but you can regenerate output with Refresh and Insert.

4. Count the themes

Build a PivotTable on the theme column to count how many comments fall under each one.

5. Verify the tags

Read 20 tagged comments, chosen from across the sheet, not just the top rows. Count how many tags you agree with. If you disagree with many of them, tighten the theme definitions in the prompt (add a short definition for each theme) and regenerate. Pay attention to the Other group, since that is where a missed theme hides.

Real Example

Scenario: A pilot of a new-manager program ended with 140 comments and you report to the head of HR on Thursday.

What you type: The formula above, with a definition added for each theme, for example "Manager support means comments about the participant's own manager backing or blocking their time."

What you get: A theme label next to each comment and a PivotTable of counts. You read 20 tags, agree with most, and fix the labels for a cluster of comments about calendar conflicts that landed in Other by adding a Scheduling theme.

Tips

• Reword the prompt if the function returns full sentences. "Reply with the theme name only" keeps the column countable.
• Comments that mention two things get one tag here. If that matters, use two columns, one per question.
• Use the counts to decide where to look. Quote the comments themselves in your report.

A Note on Your Data

Open-text comments often identify the writer through a detail such as a team, a location or a story about a manager. Strip names and identifying details before the comments go into any AI function, and keep small groups out of any breakdown you share. Whether this spreadsheet may be processed by Gemini is a question for your company's AI policy and your Workspace admin.

Tool interfaces change. If a button has moved, look for similar AI, Copilot or Gemini options in the same menu area.