How to Use AI to Analyze Survey Data (6 Steps + Prompts)

Written by

The survey closed on Friday. You have a spreadsheet with 140 rows, eight rating questions and two open text boxes full of honest, rambling answers. Your manager wants “the key takeaways” by Wednesday, and you have no idea where to start reading.

Here is how to use AI to analyze survey data without fooling yourself: export the responses, strip out names and emails, give every row an ID, and upload the file to ChatGPT, Claude or Gemini. Let it count the rating questions and break them down by group. For the open answers, have it propose a short list of themes, fix that list yourself, then let it tag every answer by ID. Check a sample against your own reading, pull the quotes from your original file, and write the summary. The AI does the sorting and counting. You decide what it means.

That is about an hour for a survey of a few hundred responses. Below are the six steps, prompts you can copy, a worked example and the checks that keep a wrong number out of your report.

No password needed – your reset opens instantly.

No password. Just your email, and you’re in.

What AI is good at here, and where it slips

Survey data comes in two kinds, and AI handles them differently.

Closed questions (ratings, multiple choice, yes/no) are numbers. ChatGPT and Claude can run real calculations on an uploaded spreadsheet, so counts, averages and breakdowns by team or region come back fast. OpenAI’s help page explains that ChatGPT writes and runs Python for this kind of work, and tells you to review the method and the assumptions before you rely on the result (OpenAI Help, Data analysis with ChatGPT).
Open answers are the slow part for a human and the strong part for AI. Sorting hundreds of comments into themes can take a whole afternoon with a highlighter. Researchers now use language models for exactly this, with people checking the work. Pew Research Center says AI tools assist its researchers with sorting open-ended survey responses into categories, while the researchers still design the analysis and interpret the data (Pew Research Center, How we are and are not using AI).

Where it slips: a chatbot that reads your answers as plain text and “estimates” a count can be off, it can merge two rows or skip one, and a quote in a summary can be quietly reworded. Every step below is built around those three failure points.

Before you start: clean the export (10 minutes)

Get the raw responses out of your survey tool as a spreadsheet. In Google Forms, open Responses and either use Link to Sheets or More > Download responses (.csv) (Google Forms Help, View and manage form responses). Microsoft Forms, SurveyMonkey and Typeform all have a similar export.

Then, before anything goes near a chatbot:

Delete names, email addresses and phone numbers. Also delete anything that points to one person, like “the only designer in the Leeds office”. Pew’s own rule is that it never exposes respondents’ personal identifying information to an open, public AI tool. That is a good rule to borrow.
Add a response ID column (R001, R002 and so on). Every check later in this guide depends on it.
Keep one row per respondent and one column per question, with plain column names in the first row. OpenAI’s guidance asks for exactly that: descriptive headers, one row per record, no empty rows splitting the data, no second table on the same sheet.
Check your company’s rules on AI tools if this is customer or employee data. A work account with data controls is a different thing from a personal free account.

Save it as a new file. Keep the original untouched, because you will pull quotes from it at the end.

Step 1: Have the AI describe the file back to you (2 minutes)

Upload the clean file. In Claude, use the + button and Add files or photos; CSV is a supported type (Claude Help, Upload files to Claude). In ChatGPT, attach it to your message the same way. Do not ask for insights yet. Ask it to tell you what it sees.

This file holds the responses to a survey of [who: e.g. our 140 employees / customers who bought in Q3].
The goal of the survey was [one sentence].

Before any analysis:
1. Tell me how many rows and columns you see.
2. List every column with its type (rating, multiple choice, open text, ID, date).
3. Flag empty rows, duplicate response IDs, and answers like "n/a" or "-".
4. Tell me which columns look like they still contain personal data.
Do not summarize the answers yet.

If the row count does not match what your survey tool says, stop and find out why before you go further. Usually it is a header row, a blank line or a test response you forgot about.

Step 2: Get the numbers on the closed questions (10 minutes)

“Analyze this survey” gets you a generic essay. Ask for the tables your report actually needs.

Calculate from the file, do not estimate.

1. For every rating question: number of answers, average, and the share of each score (1 to 5).
2. Break those down by [team / region / customer type]. Show the number of respondents (n) in every group.
3. Mark any group with fewer than 10 respondents as "small group, read with care".
4. List the 3 biggest differences between groups.

Show the results as tables. Then tell me exactly how you calculated each one.

Now check three numbers yourself. Pick one average, one percentage and one group count, and work them out in your spreadsheet with a pivot table or a COUNTIF. If the formula part scares you, our guide on using AI to write Excel formulas gets you there in a couple of minutes. If all three match, you can trust the method a lot more. If one is off, ask it to show how it got there.

Watch the small groups. If the sales team is four people and two of them gave a 1, the chatbot will happily report “50% of sales is very unhappy”. True, and almost meaningless. That is why the prompt asks it to show n everywhere.

Step 3: Turn the open answers into a theme list (10 minutes)

This is the part people get wrong. They paste 300 comments and ask “what are the main themes?” The AI produces a neat list, with counts, and nobody can tell whether those counts are real.

Split it into two jobs instead. First, only a theme list, built from a sample:

Here are 60 answers to the question "[exact question text]". Each starts with its response ID.

Propose a list of 6 to 10 themes. For each theme give:
- a short name
- one sentence that says what belongs in it
- what does NOT belong in it
- two response IDs that are clear examples

Add "Other" and "No real answer" as the last two themes.
Do not count anything yet.

[paste 60 answers with IDs]

Read that list like an editor. Merge two themes that mean the same thing, split a theme that is really two complaints in one, and rename anything vague. “Communication” is too broad to act on. “Hear about changes too late” is something a manager can fix. The list you end up with is yours, not the AI’s.

Step 4: Tag every answer, in batches, by ID (15 minutes)

Now give the final theme list back and let the AI tag every response. For a few hundred short answers, an uploaded file in ChatGPT or Claude can handle it in one go. For long answers or a few thousand rows, work in batches of 100 to 200.

Use ONLY the theme list below. Tag every answer in the column "[column name]".
An answer can get more than one theme. Use "Other" if nothing fits.

Return a table with exactly one row per response ID and two columns: Response ID, Themes.
Do not skip, merge or invent response IDs. At the end, tell me how many rows you returned.

Theme list:
[paste your edited list with the one-sentence definitions]

Check the row count against your file. Then paste the table back into your spreadsheet next to the original answers, matched on the response ID, and do the counting there with a pivot table. Counting in the sheet is exact, and you can show anyone how you got the number. In ChatGPT or Claude you can also ask it to add the theme column to the file and give you the spreadsheet back; Claude’s file tools can create an Excel file directly (Claude Help, Create and edit files with Claude).

Step 5: Check the AI against your own reading (10 minutes)

Pick 20 random responses. Tag them yourself first, then compare with the AI’s tags. If you agree on most of them, carry on. If you keep disagreeing on the same theme, the definition is the problem: tighten that one sentence and re-run the batch.

That is a small version of what professional survey teams do. For one 2026 report, Pew built a set of 1,200 answers that people had sorted by hand, ran its prompt on them, looked at every case where the model and the humans disagreed, and refined the prompt several times until the agreement was good enough (Pew Research Center, methodology for the bogus respondents report). A July 2026 report for the General Social Survey found that language models can effectively support this work, that human review stays important, and that both people and models were more accurate on factual answers than on opinions (GSS Methodological Report 150, NORC).

The practical lesson for a work survey: a comment like “the new rota is posted on Thursdays, too late to plan” is easy to tag. A comment like “fine I guess, same as always” is not, and you should expect to disagree with the AI on those. Sarcasm in particular trips it up, so read the negative and the “Other” pile yourself.

Step 6: Write the summary people will actually read (10 minutes)

Now let the AI help with the writing, using only the checked numbers.

Here are the checked results of our survey: [paste the tables from Steps 2 and 4].

Write a one-page summary for [audience: e.g. the leadership team].
- Start with the 3 findings that matter most, each with its number and n.
- For each open-answer theme, give the count and list 2 response IDs that illustrate it best. Do not write quotes yourself.
- Separate what the data shows from what we might do about it.
- Flag any finding based on a group smaller than 10 people.
Plain English, no jargon.

Notice the line about quotes. Ask for response IDs, then copy the real words from your original file. A summary that “cleans up” a respondent’s sentence is no longer a quote, and if someone checks, your whole report loses trust.

If the summary needs to become slides or a longer write-up, our guides on making a presentation with AI and writing a work report with ChatGPT pick up from here.

Already using Google Forms or Microsoft Forms? Try the built-in AI first

For a quick read, you may not need to export anything.

Google Forms with Gemini: on an eligible Google Workspace or Google AI plan, the Responses tab summarizes open text questions once there are 3 to 200 responses. It can also group answers into themes and open a Google Sheet with the theme counts and a pie chart (works best with 8 to 200 responses), and an Actionable Insights option needs at least 12 responses and 3 questions (Google Docs Editors Help, Summarize and analyze responses with Gemini in Google Forms).
Microsoft Forms with Copilot: with a Microsoft 365 account and a Copilot license, go to View responses and open Copilot to get a summary of key findings and ask follow-up questions about the data (Microsoft Support, Welcome to Copilot in Forms).

These are great for a first look. For anything that goes into a decision, you still want the checks from Steps 4 and 5: themes you have edited, counts you can trace, and quotes from the source.

A worked example (fictional, to show the flow)

Sam runs operations at a 140-person logistics company. The quarterly pulse survey got 112 responses: six ratings from 1 to 5 and one open question, “What one thing would make your work week easier?”

Step 1: Claude reports 113 rows. Row 113 is Sam’s own test response from before launch. Deleted.
Step 2: The average for “I have the information I need to do my job” is clearly lower in the warehouse than in the office. Sam rebuilds that one number in a pivot table. It matches.
Step 3: From 60 sample answers, the AI proposes nine themes. Sam merges “Scheduling” and “Rota” into one theme, “Shift rota published too late”, and splits “Tools” into “Scanners keep failing” and “Too many logins”.
Step 4: All 112 answers are tagged. The table has 112 rows. Sam counts the themes with a pivot table.
Step 5: Sam tags 20 answers by hand and disagrees with the AI on three, all of them short sarcastic replies. The themes stay; those three get fixed by hand.
Step 6: The summary leads with the rota finding, links it to the low information score in the warehouse, and quotes two real answers pulled from the original file by ID.

Sam’s report is not longer than last quarter’s. It is just traceable. When the warehouse manager asks “where does that come from?”, Sam can show the rows.

What not to paste into a chatbot

Names, emails, employee numbers, or anything else that identifies a respondent.
Small-group answers that make someone recognisable, even without a name.
Health, salary or disciplinary details, unless your company has approved a tool for that.
Answers from a survey that promised anonymity, unless your AI setup matches that promise. If you told people “only HR sees this”, a personal chatbot account is the wrong place for it.

Common mistakes

Asking for themes and counts in the same prompt. You get tidy numbers you cannot check. Theme list first, tagging second, counting in the sheet.
Trusting the first theme list. It is a draft. Your edits are what make it fit your organisation.
Reporting percentages for tiny groups without saying how many people are behind them.
Letting the AI write the quotes. Ask for IDs and copy the original words.
Skipping the “Other” pile. The surprising answer that nobody planned a theme for often lands there.
Jumping to recommendations. “The rota is published too late” is a finding. “Publish it two weeks ahead” is a decision someone with context should make.

FAQ

Can ChatGPT analyze survey results?

Yes. Upload the responses as a CSV or Excel file and it can calculate counts, averages and breakdowns, make charts, and sort open answers into themes. OpenAI asks you to review the method and the assumptions it used, which is why this guide has you check three numbers and a sample of tags yourself.

Which AI is best for survey analysis?

For most work surveys, the one you already have access to through work. ChatGPT and Claude both take uploaded spreadsheets and can run real calculations. Gemini is built into Google Forms and Copilot into Microsoft Forms, which is handy for a first look. The checks in Steps 4 and 5 matter more than the tool. If you are choosing between them, our ChatGPT tutorial for beginners and Claude tutorial for beginners show how each one works.

How do I analyze open-ended survey responses in Excel with AI?

Export the answers with a response ID column, let the AI propose and then tag themes (Steps 3 and 4), paste the tag table back next to your data in Excel, and count the themes with a pivot table or COUNTIF. Excel does the counting, the AI does the reading.

How many responses can AI handle?

An uploaded file holds far more than you could paste into a chat, and the exact upload limits are on each tool’s help pages. The practical limit is checking: with long answers or thousands of rows, work in batches and check the row count of every batch. Gemini in Google Forms generates its theme view for up to 200 responses.

Is an AI analysis accurate enough to present to leadership?

The numbers are, once you have checked them in the sheet. The themes are, once you have edited the list and tested a sample against your own reading. Say in your report that AI helped sort the open answers and that you checked it. Pew discloses its AI use in the methodology section of its reports, which is a fair standard to follow.

Can AI write the survey too?

Yes, and a well-built survey is much easier to analyze. See how to use AI to create a survey for the brief, the prompt and the test run.

Sources

Still designing the questions you will analyse later? If you need a scored knowledge check rather than open opinions, start with how to make a quiz with AI before you collect the answers.

Your next 20 minutes

Open your last survey export, delete the names and emails, add a response ID column and run the Step 1 prompt. Then take one open question and build the theme list from Step 3. In 20 minutes you will know what your people are really saying in that comment box, and you will be able to show where every number comes from.

Want to see what else is in the spreadsheet you have been avoiding? Our guide to analyzing a spreadsheet with ChatGPT uses the same checks, and using AI for market research shows how to put survey findings next to other sources. For more practical AI workflows you can use at work this week, start with the free Launchpad on our home page.