How to Analyse Survey Responses in Excel With Copilot

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Analyse Survey Responses in Excel With Copilot.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Analyse Survey Responses in Excel With Copilot.

To analyse survey responses in Excel with Copilot, put the export in a table, have Copilot propose themes for one open-text column, edit them into a codebook, then have it tag every response in a new column. Check 40 tags by hand, then ask for a PivotTable and bar chart of theme counts by customer type.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

The order matters more than the prompts. Asking Copilot "what are the main themes?" gets you a fluent paragraph with approximate numbers, and approximate numbers are what the owner then quotes at the team meeting. Tagging each row against a fixed list first means every count comes from a column you can filter, check and recount, not from a summary that reads well.

Which licence do you need for Copilot survey analysis in Excel?

Copilot in Excel is available through four routes, but Microsoft names only Microsoft 365 Copilot licence holders for the theming feature this tutorial relies on. Microsoft's FAQ lists the four routes: a Microsoft 365 Personal or Family subscription with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or a business or enterprise Microsoft 365 plan that is eligible for Copilot Chat. The theming feature has a narrower note. Microsoft's July 2026 announcement of Copilot summarising text columns and generating a category for each entry (message MC1430543) names users with a Microsoft 365 Copilot licence. If you're on one of the other routes, run the tagging step on a copy and see whether it works before planning around it.

Business licencePrice (USD), checked 20 Nov 2026
Microsoft 365 Copilot Business add-on$21 a user a month on an annual commitment ($18 for the first year on annual billing until 31 December 2026); about $22.05 paid monthly on that commitment; $25.20 month-to-month through a Microsoft partner, according to Microsoft's March 2026 partner pricing (Microsoft's own pricing page lists only the annual price)
Microsoft 365 Business Premium with Copilot$32 a user a month paid yearly (annual subscription); Microsoft's pricing page lists no monthly-term price

For one person who analyses surveys a few times a year, one licence is enough: the analysis happens in one workbook, and colleagues only need to read the result. The full licence comparison is in which licence Copilot in Excel needs and what it costs.

One older method no longer applies. Guides written in 2025 tag survey rows with the =COPILOT worksheet function. Microsoft's notice MC1454373 says that from 14 September 2026 that function, which was only ever in Insider and Frontier previews, is no longer available, and points users to the Copilot side pane. What to use in its place for other formula-style jobs is covered in the COPILOT function retirement tutorial.

How should you prepare a survey export for Copilot in Excel?

Prepare a survey export by keeping the raw data untouched, using one header row and one row per response, and giving each response an ID. The illustration is an estate agency that sends a five-question survey after every sale, purchase and new tenancy, and has 312 responses from the last six months exported from its form tool. Twenty minutes of preparation saves an hour of confused answers:

  1. Keep the raw export untouched. Copy it to a sheet called Raw and work on a second sheet. Microsoft's FAQ notes that saved Copilot changes are visible to anyone with access to the file, and that you can undo or view prior versions; a Raw sheet is quicker than either.
  2. One header row, one row per response. Delete merged title rows and any summary rows the form tool adds at the bottom.
  3. Give each response an ID. If the export has none, add a Response ID column numbered 1 to 312. Every check later depends on it.
  4. Short, plain column names. "Q4 - Is there anything we could have done better? (optional)" becomes Improve. Microsoft's guidance is that naming the columns you want analysed produces more accurate results, and short names are easier to name.
  5. Strip personal details from the text. Customers write names, phone numbers and addresses into comments. Search the text columns for @ and for digits, and replace what you find with [removed]. Delete name and email columns entirely unless you need them.
  6. Format it as a table (Ctrl+T) and name it Survey, so prompts can refer to Survey[Improve].
  7. Set calculation to automatic. Microsoft's FAQ says Copilot only edits when Formulas > Calculation Options is set to Automatic. A workbook inherited from someone else may be set to Manual.

The agency's table ended up with seven columns: Response ID, Date, Customer type (Seller, Buyer, Landlord, Tenant), Score (0 to 10, "how likely are you to recommend us"), Well (what we did well), Improve (what we could do better) and Office. Blank Improve answers stay in: "nothing" is a result. For deeper cleaning of messy exports, see cleaning customer data in Excel with Copilot.

How do you get Copilot to propose themes from survey answers?

Ask Copilot in chat-only mode to propose 6 to 8 themes from one open-text column before it tags anything. Select the Copilot icon in the lower-right corner of Excel. Copilot opens in edit mode by default; switch to chat-only mode for this step, because you want suggestions, not changes. The prompt:

Read every answer in Survey[Improve]. Propose between 6 and 8 themes
that together cover most of the answers. For each theme give:
- a short name (4 words or fewer)
- a one-line definition
- two example Response IDs
Also tell me roughly how many answers fit none of your themes.
Treat answers like "nothing", "all good" or "n/a" as their own theme.

An illustrative reply, close to what this kind of prompt returns:

1. Communication - customers wanted more frequent updates (IDs 14, 203)
2. Responsiveness - calls and emails not returned quickly (IDs 37, 118)
3. Viewings - short notice, late or cancelled viewings (IDs 9, 260)
4. Pricing advice - valuation or asking price felt wrong (IDs 51, 77)
5. Fees and paperwork - unclear fees, forms or contract steps (IDs 88, 301)
6. Marketing - photos, floor plans or listing wording (IDs 122, 190)
7. No improvement needed - "nothing", "all good" (IDs 2, 45)
About 15 answers do not fit these themes.

Two things to fix before using it. "Communication" and "Responsiveness" overlap: a customer complaining that nobody rang back is in both. For an estate agency the useful split is when it happened, not how: updates between offer and completion are one problem with one owner, and phone calls not returned is another. And "Pricing advice" mixes sellers who felt over-valued with buyers who thought a property overpriced, which are different conversations. The example IDs let you check this quickly: read the two examples for each theme before accepting it.

Step 2: turn the draft into a codebook you control

A codebook is a fixed list of themes with rules for what goes where. Copilot proposes; you decide. The agency's final version, filled in:

ThemeIncludeExclude
Offer-to-completion updatesNot knowing what was happening after an offer was accepted; chasing for newsUnreturned calls before an offer (Hard to reach)
Hard to reachCalls, emails or messages not returned; nobody availableSlow updates after an offer
Viewing arrangementsShort notice, late, cancelled or badly run viewingsComplaints about other buyers at viewings
Valuation adviceSellers or landlords unhappy with the price or rent advisedBuyers saying a property was overpriced (Other)
Fees and paperworkUnclear fees, forms, contracts, deposit stepsLawyers' or lenders' delays outside the agency's control (Other)
Marketing qualityPhotos, floor plans, descriptions, listing errors
No improvement suggested"Nothing", "all good", blank, praise onlyPraise followed by a complaint (tag the complaint)
OtherAnything that fits none of the above

Eight themes is about right for a few hundred answers. Fewer and the categories become vague; many more and each count is too small to act on. Keep Other, and expect it to hold 3% to 5% of answers; if it grows past about 10%, you're missing a theme.

How do you have Copilot tag every survey response with a theme?

Tag every response by pasting the codebook into a prompt in plan mode and asking Copilot to add a theme column, one theme per row. Microsoft's support page says that for large sets of text such as survey results, Copilot can summarise them, identify themes and sentiment, and add the results to your workbook as a new column. Switch to plan mode for this step, so Copilot shows its approach before it changes anything, then approve it. The prompt carries the codebook:

Add a column to the Survey table called Improve theme. For each row,
read Survey[Improve] and choose exactly one theme from this list,
using the rules given:

Offer-to-completion updates: no news after an offer was accepted.
Hard to reach: calls, emails or messages not returned.
Viewing arrangements: short notice, late, cancelled or badly run viewings.
Valuation advice: seller or landlord unhappy with the price or rent advised.
Fees and paperwork: unclear fees, forms, contracts or deposit steps.
Marketing quality: photos, floor plans, descriptions, listing errors.
No improvement suggested: "nothing", "all good", blank, or praise only.
Other: anything else.

If an answer mentions two themes, choose the one it complains about
most. Use only these exact names. Do not change the Improve column.

Before and after, for four illustrative rows:

IDImprove (customer's words)Improve theme
14"Once the offer went in I had to ring every week to find out anything"Offer-to-completion updates
37"Left three voicemails before anyone called me back"Hard to reach
2"Nothing, really happy"No improvement suggested
301"Wasn't clear the fee was plus extras until the contract stage"Fees and paperwork

When it finishes, check three things before anything else: the new column has 312 filled cells, every value is one of the eight exact names (a quick filter on the column shows any stray spelling such as "Hard To Reach"), and the Improve column is unchanged against the Raw sheet. If you want sentiment as well, ask for it as a separate column with three fixed values (positive, mixed, negative) rather than letting Copilot invent a scale.

How do you check Copilot's survey tags before counting them?

Check 40 randomly picked tags by hand before trusting any count, because a sample tells you whether the tags are good enough to count. Add a column with =RAND(), sort by it, and read the first 40 rows, comparing each tag with what you would have chosen. Then sort back by Response ID. The agency's first pass, illustrative:

ResultRows of 40What caused it
Agreed34
Hard to reach tagged as offer-to-completion updates2Comments about chasing that didn't say when it happened
Sarcasm read as praise1"Great communication, once a month"
Two complaints, the lesser one chosen1The "complains about most" rule is hard to apply
Debatable either way2Left as tagged

That is 34 of 40, or 85%: good enough for the big themes, not for small ones. The fixes were wording, not technology. The agency added "if the answer doesn't say whether an offer had been accepted, choose Hard to reach" to the codebook, and changed the two-theme rule to "choose the theme mentioned first". After re-running the tagging on a copy of the table and checking a fresh 40, agreement was 37 of 40.

Two further checks take a minute each. Filter to Other and read every row in it; that is where a missing theme hides. And filter to "No improvement suggested" with a Score of 6 or below: a low score with no complaint suggests the complaint went into the Well column or was written sarcastically.

How do you count and chart survey themes with Copilot in Excel?

Count the themes with a PivotTable that Copilot builds in edit mode, then cross-check one number with a formula. Ask Copilot for the counts: "Create a PivotTable on a new sheet counting Survey rows by Improve theme, with Customer type across the columns, sorted largest first." Microsoft lists creating and editing charts and PivotTables among the things Copilot in Excel does. Then cross-check one number yourself with a formula, for example =COUNTIF(Survey[Improve theme],"Hard to reach"), and confirm the PivotTable's grand total is 312.

The agency's results, illustrative:

Improve themeResponsesShareAverage score (0-10)
No improvement suggested9631%9.1
Offer-to-completion updates7123%6.1
Hard to reach4414%5.4
Viewing arrangements3110%7.2
Valuation advice268%5.9
Fees and paperwork196%6.8
Marketing quality155%7.5
Other103%7.0
Total312100%

The average score column is where the survey earns its keep. Ask Copilot to add it with AVERAGEIFS, then check one figure by hand. Offer-to-completion updates is the biggest complaint, but Hard to reach costs the most goodwill per mention (5.4). The Customer type split showed sellers made up most of the 71 update complaints, which pointed the agency at one fix: a weekly progress message to every seller from offer to completion, owned by one administrator rather than each negotiator.

For charts, ask for "a horizontal bar chart of responses by Improve theme, sorted largest first, excluding No improvement suggested". Horizontal bars suit long theme names; a pie chart with eight slices is unreadable. A second chart worth having is a stacked bar of the top four themes by Customer type. Read every axis label Copilot produces against the PivotTable: a chart is only as right as the range behind it. More on presenting results is in analysing data with Copilot.

The time, for the record: about 20 minutes of preparation, 10 for the theme list and codebook, 5 for tagging, 25 for two rounds of checking and 15 for counts and charts. Call it 75 minutes for 312 answers, against most of an afternoon reading and tallying by hand, and with a column anyone can audit afterwards.

If the PivotTable and formula side is the slow part, try The Complete Excel Course with AI Integration on Coding Liquids.

What leaves the workbook, and what to strip first

Microsoft's Learn page for work accounts says prompts, responses and data accessed through Microsoft Graph aren't used to train foundation models, and that Copilot only surfaces organisational data the user can already view. For personal Microsoft accounts, its home-apps privacy page says prompts, responses and file contents used with Copilot in Microsoft 365 apps aren't used to train foundation models either. Neither changes what you should do first: remove names, contact details and anything about health or money that customers volunteered, because the safest data is the data you never sent.

Staff names deserve a decision of their own. Comments such as "[negotiator] never rang me back" are useful for the Hard to reach count but poor evidence for judging that person. Tag the theme, and handle anything about an individual by reading the original comments yourself, privately.

When Julius is the better tool for the same survey

Julius is a separate AI data-analysis service: you upload the file, ask questions, and it runs code to answer. For survey work the comparison comes down to five points:

Copilot in ExcelJulius
CostCovered by a Copilot licence you may already havePlus listed at $20 a month for 2,000 credits; usage varies by task and model
Where the data goesStays in your Microsoft 365 environment under Microsoft's commitmentsUploaded to Julius; its security page says it may share data with AI providers to improve their models unless you opt out
RepeatabilityRe-run the prompts each quarterRuns code for each analysis, so you can ask it to repeat the same steps on next quarter's file
OutputColumns, PivotTables and charts inside the workbookCharts and tables you download or share
Best forTeams already in Excel who want an auditable columnOne-off deeper analysis, or people without a Copilot licence

Two Julius details matter for customer surveys. Its privacy policy asks users not to upload sensitive personal data such as health information, which a scrubbed survey shouldn't contain anyway. And the opt-out from sharing data with model providers is a request you make, not a default, so make it before the first upload. Julius is SOC 2 Type II certified. Whether its handling suits your data is covered in whether Julius AI is safe for customer data. Whichever tool you use, the codebook, the 40-row check and the cross-checked counts carry over unchanged.

Where Copilot's survey analysis goes wrong

Numbers quoted from the chat instead of the column

Asked "how many people complained about communication?", Copilot in chat-only mode answered "about 80". The PivotTable said 115 for the two related themes combined. The chat answer wasn't a count of the column; it was a summary. The gap surfaced in a meeting: the owner's slide said 80, a negotiator's own tally said over 100, and the meeting spent ten minutes on the discrepancy. Quote numbers only from the PivotTable or a COUNTIF. When Copilot makes a claim in chat, hover over the superscript number in its answer to see the rows it used, as Microsoft's support page suggests.

The original comments edited

A vague prompt ("tidy up the Improve column and add themes") can lead Copilot to correct spelling or shorten the customers' text. The words are your evidence; changing them breaks the audit trail. Keep the Raw sheet, say "Do not change the Improve column" in every tagging prompt, and compare a few cells with Raw after each run.

Copilot won't make changes at all

If Copilot answers but never edits, check Formulas > Calculation Options. Microsoft's FAQ says editing is only supported when it is set to Automatic. If the workbook lives in a SharePoint library that requires check-out, the FAQ says Copilot on Windows or Mac won't work with it, and Excel for the web is the workaround.

Treating the first theme list as the answer

The first list Copilot proposes reflects the words customers used, not the decisions you need to make. "Communication" was the customers' word; "offer-to-completion updates" was the agency's problem with an owner. Spend the ten minutes on the codebook. For more on turning feedback themes into fixes, analysing customer feedback surveys with AI covers the follow-through.

If you want help fitting a survey routine into how your business actually runs, I offer one-to-one AI consultations for small businesses.

Analysing survey responses in Excel with Copilot: questions answered

Can Copilot analyse survey answers that aren't in English?

Partly. It accepts many languages, but Microsoft's FAQ says Copilot was trained mostly on English sources and may not perform as well with prompts or data in other languages. If a survey mixes languages, filter the non-English answers into their own view and check every one of their tags by hand rather than relying on a sample.

Is the COPILOT worksheet function a better way to tag each row?

No, it isn't available any more. Microsoft's notice says that from 14 September 2026 the =COPILOT function, which was only in Insider and Frontier previews, is no longer available in Excel, and it points users to the Copilot side pane instead. Older guides that tag survey rows with that formula no longer work.

How many responses do I need before themes mean anything?

There is no fixed number, but small counts mislead. With 40 responses, a theme mentioned by four people is 10% and could swing on one opinion. Report counts alongside percentages, and treat any theme with fewer than about ten mentions as a lead to investigate by reading the comments, not as a finding.

Should I let Copilot rate individual staff from customer comments?

No. Customer comments that name a staff member are thin evidence for judging that person, and an AI summary adds another layer of interpretation. Tag the theme, such as hard to reach, and handle anything about an individual by reading the original comments yourself, in a normal one-to-one conversation.

Can Copilot in Excel find themes in survey responses?

Yes, if your licence route supports it. Microsoft's support page says Copilot can summarise large sets of text, identify themes and sentiment, and add the results as a new column. Ask it to propose themes first, turn them into a codebook, then tag every row against that list and check 40 tags by hand before counting.

How do I count and chart the themes once they are tagged?

Ask Copilot in edit mode for a PivotTable counting survey rows by theme, sorted largest first, then ask for a bar chart. Cross-check one number with a COUNTIF formula and confirm the PivotTable's grand total equals your number of responses. Quote counts only from the PivotTable or COUNTIF, never from a chat answer.

Further reads

Sources: Microsoft support pages (Copilot in Excel FAQ, Get started with Copilot in Excel, Get insights from text-based data with Copilot in Excel, Copilot in Microsoft 365 apps for home privacy); Microsoft Learn (Data, Privacy, and Security for Microsoft Copilot); Microsoft 365 admin notices MC1430543 and MC1454373; Microsoft Copilot business pricing page; Julius pricing, security, privacy policy and credits documentation. Checked 17-18 Nov 2026.

Rather not work this out alone?

An AI implementation consultation is a one-to-one call with me, not an agency or a sales team. You leave knowing what AI you can use in your business and where to start.

Book a 1:1 call with me