Summary

Your form has responses. This article takes you from the Responses tab to an Excel workbook you can actually analyze — one row per response, multi-select answers broken into countable columns, free text tagged into themes, and a PivotTable that answers the cross-question your form summary cannot. It also covers sharing results read-only and deleting responses safely.

In this guide

• 1. Read the Responses Tab Before You Export
• 2. Export the Responses to Excel
• 3. Make the Raw Export Usable
• 4. Split Multi-Select Answers and Count Text Answers
• 5. Summarize the Results with a PivotTable
• 6. Share a Results Summary Without Giving Edit Access
• 7. Delete Responses and Understand the Anonymity Caveat

Before You Start

• A Microsoft 365 work or school account, and a form you created or co-own. If you have not built the form yet, start with How to Create and Manage a Survey with Microsoft Forms.
• At least one submitted response. The export and the charts do not appear until a response arrives.
• Excel for the web is enough for most of this. The PivotTable and Text to Columns steps are smoother in the Excel desktop app.
• No administrator rights required.

Instructions

1. Read the Responses Tab Before You Export

The built-in summary answers a surprising number of questions in ten seconds. Knowing where it stops is what tells you whether you need the export at all.

1. Go to https://forms.office.com and sign in with your work account.
2. Click your form to open it, then click the Responses tab at the top of the editor.
3. Read the three figures across the top: the number of Responses, the Average time to complete, and the form's Status — Active while it is still accepting answers.
4. Scroll down. Each question has its own block: choice questions show a chart with a count and a percentage per option, rating questions show an average, and text questions show only the most recent handful of answers with a More details link below them.
5. Click View results to page through complete individual responses one at a time, including the respondent's name when the form records it.

What the summary does not tell you. Three limits push people to the export:

• It reports one question at a time. It cannot answer "of the people who chose Warehouse, how many rated training below 3?" Cross-tabulation needs a PivotTable.
• Text answers are sampled, not listed. The block shows a few recent answers. Reading all 180 of them means exporting.
• Percentages are calculated against the people who answered that question, not against your total response count. On a form with branching logic this gap gets wide — see How to Use Branching Logic in Microsoft Forms for why.

2. Export the Responses to Excel

There are two ways out of Forms and they behave differently. Picking the wrong one is the most common source of "my numbers changed overnight".

1. On the Responses tab, click Open in Excel. This creates a workbook that stays connected to the form, so responses that arrive later show up in it.
2. Note where that workbook landed:
   • A personal form saves to your OneDrive, under Apps > Microsoft Forms > your form name.
   • A group form — one you created from a Teams channel or inside a Microsoft 365 group — saves to that group's SharePoint document library. Everyone with access to the group opens the same file and sees the same live numbers, which is what you want for a team-owned survey.
3. For a copy you can reshape without consequences, open the More options menu (⋯) on the Responses tab and click Download a copy. Your browser saves a static .xlsx file to your Downloads folder, frozen at the moment you clicked.
4. In a connected workbook, pull in newer responses at any time: in Excel desktop, click Data > Refresh All. In Excel for the web, close and reopen the file from Open in Excel.

Tip: Use the connected workbook to watch results arrive. Use a downloaded copy for the analysis in Steps 3 to 5. Heavy reshaping — deleting columns, splitting answers, inserting helper columns — is far safer on a file that nothing is writing new rows into.

What the export actually contains. The first columns are always ID, Start time, Completion time, Email, and Name, then one column per question in the order they appear on the form. Each response is one row, which is exactly the shape Excel wants. Email and Name are blank on an anonymous form.

3. Make the Raw Export Usable

The export is structurally correct and visually unusable — the headers are entire questions, and the timestamps are unreadable. Five minutes here saves an hour later.

1. Open the downloaded workbook in the Excel desktop app.
2. Click any cell inside the data and press Ctrl + T. The Create Table dialog opens with the range already selected.
3. Confirm My table has headers is ticked and click OK. Filter arrows appear on every header. See How to Format Data as a Table in Excel for what this buys you.
4. Shorten the headers. Click each question header cell and replace the full question text with a short label such as Dept or Training score. Keep a copy of the original wording on a second sheet so you can quote questions accurately in your write-up.
5. Fix the timestamps. Select the Start time and Completion time columns, then on the Home tab open the Number Format dropdown and choose Short Date — or More Number Formats if you need the time of day as well.
6. Sort or filter from the header arrows to sanity-check the data. See How to Sort and Filter Data in Excel.

Note: If you added a question after responses started arriving, the rows collected before that change are blank in the new column. That is expected — those people were never asked. Do not fill the gaps with zeros; a blank and a zero mean different things and a PivotTable treats them differently.

4. Split Multi-Select Answers and Count Text Answers

Two question types arrive in a shape Excel cannot count. Both have a fix.

Multi-select choice answers arrive as one cell holding every selected option, separated by semicolons — Excel;Teams;Power BI. To read them, split them; to count them, flag them.

1. To read them side by side, first insert enough blank columns to the right of the answer column to hold the widest response, or the split overwrites the column next to it.
2. Select the answer column, click Data > Text to Columns, choose Delimited, and click Next.
3. Tick Semicolon, untick everything else, click Next, then Finish. Each answer lands in its own column.
4. To count them, leave the original column alone and add one helper column per option instead. Splitting puts answers in different columns on different rows, so counting across split columns is unreliable. In a new column headed Wants Excel, enter:

=IF(ISNUMBER(SEARCH("Excel",F2)),1,0)

5. Fill the formula down the column, then read the total from the status bar or with =SUM at the foot of the column. Repeat for each option you care about.

Warning about wildcards: SEARCH and COUNTIF with "*Excel*" also match an option called Excel Advanced, which inflates the count. When one option's wording sits inside another's, either match the full text with EXACT, or rename the options in the form before you collect more responses.

Free-text answers cannot be pivoted as they stand — 180 people write 180 different sentences. Turn them into categories:

1. Read 20 to 30 answers and write down the four to six themes that keep recurring, such as Pricing, Training, Speed, Support.
2. Add a column next to the text column headed Theme.
3. Select the whole Theme column and click Data > Data Validation. Under Allow, choose List, and type your themes into Source separated by commas. Click OK.
4. Work down the column choosing a theme from the dropdown. The dropdown is the point — typing freely produces Pricing, pricing, and price as three separate rows in your PivotTable.
5. For a quick keyword count without categorizing every row, use the same SEARCH formula from above against the text column.

5. Summarize the Results with a PivotTable

This is the step the Responses tab cannot do for you: putting two questions against each other.

1. Click any cell in your table, then click Insert > PivotTable.
2. In the dialog, confirm the table name in Table/Range, choose New Worksheet, and click OK. The PivotTable Fields pane opens on the right.
3. Drag the field you want to count — Dept, or your Theme column — into the Rows box.
4. Drag ID into the Values box. It shows as Count of ID, which is your response count per row. ID is safe to count because every response has one.
5. To cross-tabulate, drag a second question into the Columns box. You now have a grid: departments down the side, satisfaction ratings across the top, counts in the middle.
6. Drag any question into the Filters box to slice the whole grid by it.
7. Right-click any number and choose Show Values As > % of Row Total to convert counts into percentages.
8. After new responses arrive and you refresh the data, click PivotTable Analyze > Refresh to update the summary.

Full detail on building and reshaping these lives in How to Create a PivotTable in Excel.

Verification: Add up the counts in your PivotTable's grand total and compare it with the response count at the top of the Responses tab. If the PivotTable total is lower, a question was skipped by some respondents and those rows are blank. If it is higher, you have pivoted a multi-select column where one person contributed several answers.

6. Share a Results Summary Without Giving Edit Access

Sending the wrong link hands a colleague the ability to rewrite your questions while responses are still coming in.

1. On the Responses tab, open the More options menu (⋯) and click Share results — labelled Get a summary link in some builds.
2. Turn the toggle on and click Copy. Paste that link into an email or a Teams message.
3. Recipients see the summary charts and the individual responses read-only. They cannot open the editor, change questions, or delete responses.

Before you send it, check two things:

• The link exposes names on a form that records them. Anyone who receives it can forward it onward within your organization. If the answers were sensitive, share the Excel workbook with named people instead.
• It is not the collaborate link. The Share button on the form editor with Get a link to view and edit grants co-ownership of the form itself. That is the one to avoid here.

For a tighter circle, share the workbook rather than the summary: open it in OneDrive or SharePoint, click Share, and set the permission to Can view before sending.

7. Delete Responses and Understand the Anonymity Caveat

Deletion in Forms is immediate and final. Read this section before you use it.

1. To remove a single response, click View results on the Responses tab, page to the response, and click the Delete response icon.
2. To clear everything, open the More options menu (⋯) on the Responses tab and click Delete all responses. Confirm the prompt.
3. Export first. There is no recycle bin for Forms responses and no undo.

What anonymous actually means. Four points, and people are caught by all four:

• A form set to Anyone can respond collects no identity at all. The Email and Name columns come through blank.
• A form limited to Only people in my organization records the signed-in name unless you turn off Record name in the form's Settings. Most people never open that setting.
• Switching a form to anonymous after collecting responses does not strip the names already gathered. Existing rows keep them. Deleting those responses is the only way to remove them from the form, and it does not touch exports anyone has already taken.
• A form can be technically anonymous and still identify people. If you asked for department, job title, and location, one person in a three-person team is identifiable from their answers alone. Say so up front when you promise anonymity, or drop the demographic questions.

Troubleshooting

WARNING

Delete all responses is permanent. Forms has no recycle bin and no undo, and a connected workbook does not preserve the deleted rows either — it refreshes to match the form. Take a Download a copy export and confirm the row count before you delete anything.

Symptom / Error	Potential Cause	Solution
Open in Excel is missing or does nothing	The form has no responses yet	The export and the charts appear once the first response arrives. Check the response count at the top of the Responses tab.
New responses are not showing in my workbook	Working from a downloaded snapshot	A downloaded copy is frozen by design. Click Data > Refresh All in the connected workbook, or reopen it with Open in Excel.
A whole column is blank for older responses	The question was added after collection started	Expected behaviour. Leave the cells empty and filter to the date the question went live before charting it.
Multi-select counts add up to more than the response count	One cell holds several answers	Each selection is counted separately. Take your response total from a count of ID, never from the sum of the option columns.
A keyword count returns too many rows	One option's wording sits inside another's	"*Excel*" also matches Excel Advanced. Match the full answer with EXACT, or rename the overlapping options in the form.
Share results is greyed out	Summary links are turned off for your organization	Tell IT: "Please enable external and internal summary result links for Microsoft Forms in the Forms admin settings." Until then, share the Excel workbook from OneDrive with Can view.
Names appear on a form you believed was anonymous	Record name was on	Open the form's Settings and turn off Record name for future responses. Rows already collected keep their names — delete and recollect if the answers must be anonymous.

Related Articles

• How to Create and Manage a Survey with Microsoft Forms
• How to Use Branching Logic in Microsoft Forms
• How to Create a PivotTable in Excel
• How to Format Data as a Table in Excel
• How to Sort and Filter Data in Excel
• How to Create and Use Polls in Teams
• How to Use Power Automate Basics in Microsoft 365

Did this solve your problem?