How to export Microsoft Forms responses to Excel

Exporting Microsoft Forms responses to Excel looks like one button. It is three, they sit behind the same control, and they produce workbooks that behave differently once anybody starts working in them. Choosing the wrong one is how a team ends up with a file that stopped updating three weeks ago, or with two copies of the same data that no longer agree.

The steps are short. What follows is mostly about the differences between the three options, what the exported columns actually contain, and the specific conditions under which the connection to the form stops working. Those conditions are documented, and most of them can be avoided by deciding correctly at the start.

Three exports, and what each one is for

From the form, open Responses and go to the Responses Overview screen. The control is Open results in Excel, with a dropdown arrow next to it offering two more options.

Microsoft's documentation is explicit about the consequence of each. Both of the first two store the workbook automatically in OneDrive for work or school, with group forms saved to SharePoint instead, and both contain a live data connection to the form, so new response data is reflected in the workbook. The third produces an offline workbook that has no relationship or connection to the form.

Two practical notes. Open in Excel Desktop is stated as available to all Current Channel Excel users running Version 2410, build 16.0.18227.20000, or later, checkable through File and then Account inside Excel, with Mac support described as coming in a later update. And if the button reads Open in Excel rather than Open results in Excel, the live connection has already been disconnected, and the workbook has to be opened so a new data connection can be set up.

The choice is simpler than it looks. A workbook that somebody will keep open and work in wants the live connection. A file being sent outside the organisation, attached to a report, or archived at the end of a process wants Download a copy, because a connected workbook handed to an outsider is a workbook that will not update for them and may not open as expected.

What the exported columns contain

The layout is fixed, and knowing it saves a round of surprises.

Each question becomes a column and each response becomes a row. The first five columns hold the respondent ID, the start and completion times for each response, and the name and email address of the respondent. The question answers follow from column six onwards.

Three consequences follow from that.

Names and email addresses are absent on anonymous forms. Microsoft states plainly that they will not be displayed if the form is set to accept anonymous responses. This surprises people at exactly the wrong moment, usually when a reply is needed and there is nothing to reply to. Where responses need answering, the contact field has to be a question on the form rather than something the export is expected to supply.

The respondent ID is the only reliable key. It is unique per response in the form, and it appears in the first column of the workbook. Where a row has to be traced back to the original response in Forms, that number is how it is done, and it is also the identifier to use when matching against anything else. Row order is not a key.

Edits in the workbook do not travel back. Changes made to the workbook will not modify the content of the form. A correction typed into the spreadsheet exists only in the spreadsheet, so the form and the file begin to diverge from the first edit. Decide which is authoritative and say so before anybody relies on it.

Who can refresh the data

This is the limit that breaks the most workflows, and it is not obvious from the interface.

Microsoft states that the data update can only be triggered in Excel by the form owner and by other users in the organisation who have edit permissions to the workbook, and that external users from outside the organisation cannot trigger the data update. A connected workbook shared with a contractor, a client or a partner will therefore show whatever data it held when they received it, with no indication that it is stale.

There is a second failure that looks identical and has a different cause. If the workbook is deleted, corrupted, or otherwise unable to show current data, Forms raises an alert offering to create a new workbook that syncs with the most recent responses, and the new workbook appears next to the original in OneDrive for work or school or SharePoint. That behaviour is useful, but it leaves two similarly named files in the same folder, and somebody will later open the wrong one.

One documented configuration prevents sync entirely. Microsoft states that workbook sync issues will occur where content approval is required for items in SharePoint: if the library option requiring content approval for submitted items is set to yes, workbooks will not sync. On a governed SharePoint site this is the first thing to check when a connected workbook has silently stopped updating, because nothing in Excel explains it.

Response volume changes what is available

Two ceilings are worth knowing before designing anything around the export.

For an organisational account, a form can receive up to 5,000,000 responses. For a form created under a Microsoft personal account, the limit is 200 responses on a free account and 1,000 on a paid one. Microsoft's own suggestion for collecting more than the limit allows is to export the existing responses to an Excel workbook and then clear them from the survey, which frees the form to collect again. That works, and it means the exported workbook becomes the only copy, which raises the stakes on where it is stored.

Above 50,000 responses, the export still works but the Forms interface changes. Microsoft states that all responses can be exported as a .csv for analysis, while summary charts and graphs, viewing individual responses from the Forms site, printing, and sharing a summary link are not supported for forms above that figure, along with manual grading and commenting for quizzes. A high volume registration or feedback form therefore loses the ability to look at one response in the Forms interface, which pushes all individual handling into the exported file.

There is also a cap on the aggregation result API. Microsoft publishes it as responses above 100,000, or responses multiplied by question count above 10 million, for business environments, and 70,000 responses or 5 million for other environments.

Cleaning the export before analysing it

Three shapes in the exported data resist analysis, and all three are cheaper to handle the first time than the fifth.

Questions that allow several answers arrive as one cell containing every selected option separated by semicolons. A pivot table treats each combination as a distinct value, so three people who all chose the same two options in a different order appear as different answers. Splitting that column into one column per option, with a one or a zero in each, is the step that makes the question countable. It is also a reason to prefer several yes or no questions over one long multiple selection question when the answers are going to be counted.

Free text answers can be long. Microsoft allows up to 4,000 characters per response to a single question, with a ceiling of 200,000 characters for the total responses to a form. A column holding paragraphs makes the whole sheet unreadable, so move those columns to the right of the numeric ones, or to their own sheet keyed on respondent ID, before doing anything else.

Times need care. The start and completion times in the first five columns are the basis of the most useful derived figure in the file, which is how long each person took. Long completion times cluster around the questions people struggle with, and a handful of very short ones usually indicate someone clicking through. Compute the difference once, in its own column, rather than eyeballing two timestamps.

Convert the range to an Excel table before adding anything. A connected workbook that refreshes into a plain range will push formulas out of alignment the moment the row count changes.

When the export is not the answer

The export is the right tool for analysis. It is a poor tool for handling, and the difference is worth naming because most teams reach for it while trying to do the second thing.

Analysis asks what the answers look like in aggregate. A workbook is ideal: filter, pivot, chart, done. Handling asks what happened to response number 47, who is dealing with it, whether a reply was sent, and what it said. A workbook answers none of those, and adding columns for them creates the familiar set of problems: a name typed into an owner column is a label rather than an assignment, two people editing the same row within a minute produce one surviving edit, and the reply itself lives in a mailbox where nobody else can read it.

The usual next step is a Power Automate flow. Microsoft lists adding form responses to an Excel worksheet among the common uses of a form in a flow, alongside sending an email when a response arrives and sending an approval request with the form details. Those flows work and are worth building. What they do not change is that the record of the work and the record of the answers remain in different places, so somebody has to keep them agreeing.

Where the handling matters more than the charts, the alternative is to stop moving the data at all: keep each response where it arrived, with its own status, its own named owner and its replies attached, and export to Excel only when someone actually wants to analyse something. What a response looks like when the reply lives on it is the part worth comparing against a spreadsheet of rows, and a run through a real submission makes the difference clear faster than a feature list.

What to do first

Pick Download a copy for anything leaving the organisation or being archived, and Open results in Excel only for a workbook that stays inside it and needs to stay current. If a connected workbook has stopped updating, check whether content approval is switched on for the SharePoint library before looking anywhere else. And where the export exists so that somebody can track who is answering each response, that job belongs on the response itself, whether through Halict or any tool built the same way.

Q1. How do you export Microsoft Forms responses to Excel?

Open the form, go to Responses and the Responses Overview screen, then use Open results in Excel. The dropdown arrow next to it offers Open in Excel Desktop and Download a copy. The first two store the workbook in OneDrive for work or school, or in SharePoint for group forms, with a live connection to the form. Download a copy produces an offline file with no connection.

Q2. What is the difference between Open results in Excel and Download a copy?

The connection. Microsoft states that Open results in Excel and Open in Excel Desktop both store the workbook in OneDrive for work or school, or SharePoint for group forms, and both keep a live data connection so new responses are reflected. Download a copy opens an offline workbook that has no relationship or connection to the form, which is the right choice for anything sent outside the organisation or archived.

Q3. Why are the name and email columns empty?

Because the form accepts anonymous responses. Microsoft states that names and email addresses will not be displayed in that case. The export cannot supply contact details the form never collected, so where responses have to be answered, an email question has to be on the form itself.

Q4. Why has the connected workbook stopped updating?

Three documented causes. External users from outside the organisation cannot trigger the data update at all, so only the form owner and colleagues with edit permissions to the workbook can refresh it. The workbook may have been deleted or corrupted, in which case Forms offers to create a new synced workbook next to the original. And Microsoft states that workbooks will not sync where a SharePoint library is set to require content approval for submitted items.

Q5. Do edits in the exported workbook change the form?

No. Microsoft states that changes made to the workbook will not modify the content of the form. The two records diverge from the first correction, so decide which one is authoritative before anyone relies on the data.

Q6. What happens above 50,000 responses?

The .csv export of all responses still works, but Microsoft states that summary charts and graphs, viewing individual responses from the Forms site, printing, sharing a summary link, and manual grading for quizzes are not supported above that figure. Individual handling then has to happen outside Forms.

Q7. How many responses can a form collect before the export is the only copy?

An organisational account allows up to 5,000,000 responses per form. A form created under a Microsoft personal account is limited to 200 responses on a free account and 1,000 on a paid one. Microsoft's suggestion for collecting more is to export the existing responses and then clear them from the form, which makes the exported workbook the only copy and worth storing carefully.