How to sync responses on Microsoft Forms with Excel

Want to sync responses from Microsoft Form to an Excel spreadsheet ? Here's how to do that incredibly easily?

How to sync responses on Microsoft Forms with Excel Picture 1

There's nothing like writing online survey responses directly into an Excel spreadsheet in real time. You can enjoy this feature if you use Microsoft Forms.

How to export Microsoft Form responses to Excel

Exporting Microsoft Forms responses to Excel helps you get the most up-to-date survey responses. It involves uploading survey responses to a computer as an Excel file. The process is easy if you have already created a survey form on Microsoft:

1. In Microsoft Forms, go to Responses on the top right.

How to sync responses on Microsoft Forms with Excel Picture 2

2. Click Open in Excel on the right side of the screen to upload the current response to your computer.

3. You have to repeat this process every time you want to get feedback update.

But this method is not the best, because you have to reset the process often to update the spreadsheet. That means you have to download a new Excel file every time someone sends a response. That action can be time-consuming and labor-intensive, especially when multiple people are responding in real-time.

To make this easier, you can automate the above process using OneDrive for Business or Power Automate, as described in the following sections.

How to sync Microsoft Forms with Excel via OneDrive for Business

You can dynamically update Excel spreadsheets if you are using a Microsoft 365 Business account. To do this, you'll create a survey form, which can dynamically write responses to Excel, directly from OneDrive.

When using OneDrive for Business to create Microsoft Forms for Excel, it causes the Excel spreadsheet version of the form to contain columns with the corresponding form field properties.

To create an Excel form that automatically updates Microsoft Forms responses in real time:

1. Sign in to Microsoft 365 Business Admin Center .
2. Click Show All in the left sidebar. Scroll down and go to SharePoint .
3. Once in SharePoint , click the application open icon (square dot) on the upper left. Then, select OneDrive in the left sidebar. This action takes you to the OneDrive for Business dashboard .
4. Inside OneDrive, click New .
5. Select Forms for Excel .
6. Name the survey and click Create .
7. OneDrive will launch in your browser, where you can create the form fields you want.
8. Go back to OneDrive and go to My files from the left sidebar. An Excel spreadsheet containing the form name will appear in OneDrive.
9. After creating and sharing the survey, open the Excel spreadsheet online and you will see the new data as they are updated in real time.
10. If you share an Excel spreadsheet with team members, they will also receive real-time feedback updates.

How to sync responses on Microsoft Forms with Excel Picture 3

How to sync Microsoft Forms responses to Excel via Power Automate

Power Automate is a powerful daily task automation tool. It also provides a great way for you to sync Microsoft with Excel. This is the best method if you already share the survey form.

It involves creating an automated cloud flow in Power Automate. However, you must also have a Microsoft Business account to use this feature. Follow the steps below to connect Microsoft Forms to Excel automatically using Power Automate:

Insert copy into Excel workbook online

The online Excel workbook must be in tabular format, because Forms only records tables in Excel spreadsheets

Before you start, create a new Excel table in OneDrive. Make sure you access OneDrive via your Microsoft Business account.

1. Click Show All in the left sidebar of the Microsoft 365 admin center. Then go to SharePoint. Click the app's icon on the top left and select OneDrive .
   Click New at the top left of OneDrive .
2. Select Excel workbook .
3. From the Excel spreadsheet opened in the browser, click Saved at the top left. Then enter the requested name in the File Name field . Click any space to close the modal box.
4. Now create the columns, make sure they respond to the fields in the survey form at Microsoft. For example, if you shared a form that needed email and gender, the Excel workbook would have these two columns.
5. Select those columns, then go to the Insert tab on the ribbon and click Table .
6. Check My table has headers and click OK .

How to sync responses on Microsoft Forms with Excel Picture 4

Sync Microsoft Forms with Excel using Power Automate

1. Sign in to your Power Automate account .
2. Click Create in the left sidebar.
3. Select Automated cloud flow .
4. Next, click When a response is submitted in the modal box.
5. Then click Create at the bottom of the opened modal box.
6. Click Sign in if prompted. Make sure you use your Microsoft 365 Business account to sign in if you're using a personal Microsoft account. You can do this by clicking Use another account on the login box.
7. Once logged in, click Pick a form in Power Automate .
8. Next, select the form you want to automate.
9. Click New step . Then use the search bar to find 'forms'.
10. Click Microsoft Forms , select Get response details under Actions .
11. Click the Form Id field and select the survey form again. Then select Response Id from the Response Id field .
12. Click New step . Then select Excel Online .
13. Next, select Add a row into a table from the options in Actions .
14. Under Location , select OneDrive for Business .
15. Select OndeDrive from the Document Library.
16. Click the file browser on the File field and select the previously uploaded Excel sheet. Or you can select files uploaded to OneDrive from your computer if you're using a previously exported spreadsheet.
17. Select the table name from Excel. New fields corresponding to each Excel column will appear. They are the same as the fields in the survey form.
18. Click on each new field and select its corresponding form field from the Dynamic content selection .
19. Finally, click Save .

How to sync responses on Microsoft Forms with Excel Picture 5

It's done.

Above are two ways to synchronize responses on Microsoft Forms with Excel. Hope the article is useful to you.

David Pac

Update 24 July 2023