Featured, Microsoft 365, Microsoft Excel, Office 2019, Office 2021 / Office LTSC, Office 365 / Forms /

The best ways to get Microsoft Forms results into Excel

13 January 2023

Here are the best ways to get results from Microsoft Forms into an Excel workbook. Sadly they aren’t the elegant and efficient ways available in modern Microsoft Excel 365, 2021 or 2019 for Windows.

We’ll explain how to get Microsoft Forms results into Excel for better filtering and analysis. Then we’ll explain how Microsoft could make the connection a lot better.

Export Forms results to Excel

Forms creators can see all the results on the Responses tab.

Scroll down to see chart summaries of answers to each question.

View Results – shows each responders answers

Open in Excel – makes an Excel workbook of all results to download.

Forms results in Excel

The Forms workbook opens in Excel as a table. Each column is an answer. Each row is someones response.

There are some additional fields at the start for details collected automatically: ID, Start and Completion Time, Email and Name. Savvy users have noted the many missing data fields that could be included from browser data.

The important column for Excel users is ‘ID’ – it’s a unique number for each response. That can be vital later so don’t delete it.

Making charts from the Forms data is simple and no different from any other table. Select a column then Insert | Chart

That’s the recommended and simple way, but it’s not the best.

Forms results over time

Often survey results come in over time. Each time you download the Excel workbook you get new results as well as older responses.

The name of the Excel workbook says which responses are included e.g Staff-Feedback (1-52).xlsx

That means it’s better to separate the response workbook from the Excel charts and filters you create from it. The Excel trick is to separate the incoming data from analysis and charts.

Done right, you can make your charts and analysis once, then plug in the latest Forms responses.

Forms download into Get & Transform

Make a new Excel workbook and link the Forms download .xlsx into it. In Excel for Windows, do that using Get and Transform.

Go to Data | Get Data | From File | From workbook

Choose the Table to import and load into PowerQuery

PowerQuery tricks with Microsoft forms

Using Get & Transform with PowerQuery gives you more control over the imported data into Excel. Here’s a few suggestions.

Time to Fillin Form and other calculated columns

Just one of Powerquery’s tricks is adding calculated columns. This can be done in the final Excel table, but it’s much faster and efficient to do this in PowerQuery before the table is added into a worksheet.

A simple example is the time it takes to complete the Form. Forms provides both the Start and Completion Time so the calculation =[Completion time]-[Start time] is trivial. From Add Column | Custom Column.

PowerQuery adds the new column, however it uses the ‘catch-all’ Any data type. Go to Transform and change the Data Type to Duration.

Protect against duplicate responses

If you’re importing multiple workbooks from the same Form, there’s a risk of double-counting a response.

Prevent that using Home | Remove Rows | Remove Duplicates using the ID column, or Email/Name if available.

Delete unwanted columns

Some of the columns/fields in the workbook from Forms aren’t needed.

If responses are anonymous, the Email and Name columns are irrelevant.

Perhaps the Start or Completion time isn’t needed either.

Use Home | Remove Columns to delete those fields so they don’t clutter up the final table in Excel.

Forms does NOT play nice with Excel

The integration between Microsoft Forms and Excel is very poor. It’s almost as if the Forms and Excel developers haven’t worked together, even though they work for the same company!

Just a few things you’d expect from a proper integration of a Microsoft product with Microsoft Excel and PowerQuery.

Direct data link Modern Excel has many direct data sources available including seven different types from Microsoft Azure alone. Why can’t Forms use one of those to let Excel directly import the latest results without manually downloading a file?

Consistent File name PowerQuery can be setup to import the latest version of a source file, but only with the same file name. Forms changes the XLSX file name each time (with the number of responses).

There is a workaround for this, if the user knows how to import data from a special folder with a changing file name. Our book Real Time Excel shows how it’s done in the chapter on Historical Stock Prices.

We could go on about Forms many limitations. So far, Forms seems to be focused on showy presentation and frippery for novices instead of truly useful connections for the millions of Office users around the world.

Microsoft Forms are now for everyone