Getting Microsoft Forms results into Excel

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


Microsoft Forms can export survey/quiz results to an Excel workbook, here’s how to do that easy and efficiently.

The current method of getting Microsoft Forms results into Excel isn’t very elegant. Surprisingly, it doesn’t make use of Excel’s excellent data import and update features.

At the moment, you have to download an Excel worksheet with the Form results to date.  There’s no option to get the results as an updatable data feed into Excel’s PowerQuery. Hopefully it’s on the developers ‘To Do’ list.

Until Forms integration with Excel improves, here’s the best way to do it.

In short:

Separate the downloaded data source (‘Results workbook’) from any PivotTables and charts you make (‘Analysis workbook’).
Download the Excel file to a ‘Results workbook’ on your computer.  Make a separate Excel worksheet (‘Analysis workbook’) and link it to the current results worksheet using Get & Transform.  When more results arrive, download another results worksheet to the same file name/location then update your data link.

Download Forms results

Download an Excel workbook with all the survey/quiz results to date.  On the Forms admin pages go to the Responses tab then ‘Open in Excel’.

getting microsoft forms results into excel microsoft excel 26879 - Getting Microsoft Forms results into Excel

Over time each download has a slightly different name.  The workbook download is the Form name plus the range of results included. For example; Geography Quiz (1-52).xlsx then a later download might be Geography Quiz (1-100).xslx

Instead of saving to your computer with a different name each time, save to the same file name and location.

Make Analysis workbook

Now make a separate Excel workbook for your analysis and collation of survey data.

Go to Data | Get and Transform | Get Data | From File | From Workbook then choose the results workbook you save from the Forms site.

The PowerQuery navigator will appear to select what sheets or tables to use from the workbook.

getting microsoft forms results into excel microsoft excel 26880 - Getting Microsoft Forms results into Excel

In this case the Table and Sheet are the same data. Choose the Table1 option then Load.

The survey results are loaded into a worksheet/tab. We rename that tab to something like ‘Raw Data’ for clarity.

Make a chart

Now the survey data is loaded, go ahead and make all the Charts, PivotTables and PivotCharts you like.

For example, select a single results column, the Insert | Recommended Charts.  Most likely Excel will suggest a simple bar chart.

getting microsoft forms results into excel microsoft excel 26881 - Getting Microsoft Forms results into Excel

Advanced users can go into PowerQuery to make separate queries and filters on the incoming results.

Updating your worksheet

This is where separating the survey results from the analysis worksheet pays off.

Anytime later, return to the Forms site and download the survey/quiz results again.  That workbook will have both the previous and latest results in one file.

Save the new .xlsx to the same name and location as your previous survey results (i.e. overwrite the previous results workbook).

In the analysis worksheet, refresh the query from Date | Refresh All. That will update the survey results from the newly saved results workbook. All your PivotTables, charts etc will be updated to reflect the latest results.

getting microsoft forms results into excel microsoft excel 26882 - Getting Microsoft Forms results into Excel

Time to complete Form

Time to complete the Form can be shown from PowerQuery, Add Column | Custom Column and the formula [Completion time]-[Start time]

getting microsoft forms results into excel microsoft excel 26883 - Getting Microsoft Forms results into Excel

Other things to try

As usual there are things to do beyond the basics. For example, the current results include unused Email and Name columns.  To tidy up the table you see, those columns can be removed (hidden) in the PowerQuery.

getting microsoft forms results into excel microsoft excel 26884 - Getting Microsoft Forms results into Excel

Incoming dates are automatically converted into Excel date format.    Use the Transform tab to change Start or Completion time to Date Only or Time Only.

getting microsoft forms results into excel microsoft excel 26885 - Getting Microsoft Forms results into Excel

Or change the times to another time zone by adding/subtracting hours.


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.