At long last, there’s a way to get Microsoft Forms results synchronized with an Excel workbook. It’s not what Excel users were expecting but it’s a start.
Microsoft is calling this a ‘highly anticipated feature’ which is corporate weasel term for a long-standing customer request that’s finally fulfilled (in part).
The Forms responses page should soon have an ‘Open results in Excel’ option at the top.
That option isn’t appearing on our test machines but presumably will appear soon. (it hasn’t appeared even a week after Microsoft’s announcement … Grrrr).
Click on ‘Open results in Excel’ to open (create) a spreadsheet in Excel on the web. That spreadsheet has a live link to Forms so new responses to the form will automatically appear in the spreadsheet as new rows at the bottom of the table. That’s what Forms users have been demanding since Day One.
Live updates only with Excel for the web
The spreadsheet can be opened in Excel desktop or mobile apps, just like any Excel doc saved on OneDrive BUT automatic updates of responses will only happen when the sheet is open in Excel for the web.
Workaround?
The feature isn’t available yet but it’s likely that automatic updating on other Excels is possible with a little trickery. Open the Forms results workbook in Excel for the web to maintain the live update link and leave that web page open. That should keep the Forms response data link working.
Then open the same workbook on another Excel app which should show the same updated results. Or make a workbook with PowerQuery linking to the Forms results workbook to import the data.
If the ‘Open in Excel’ option appears for you, give that a try and let us know.
A partial solution
Office Watch has commented on the poor links between Forms and Excel for a long time. It’s a shame that this long-overdue feature is a partial fix that doesn’t make use of Excel’s power.
“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? …
Getting Microsoft Forms results into Excel
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. ”
The new feature goes part way to resolving the “Forms to Excel” problem. Microsoft seems reluctant to create a link from Forms to Excel’s PowerQuery which can then import the Forms data and present it in a sheet. Perhaps they are trying to control the load on Forms servers by preventing direct links between PowerQuery and Forms responses database?
By all means, provide the ‘Open in Excel for the web’ option for less experienced users but how about a simple live link that modern Excel for Windows/Mac can consume directly?
The best ways to get Microsoft Forms results into Excel
Getting Microsoft Forms results into Excel
Microsoft Forms are now for everyone
