Data Import & Export

How to Populate an Excel Workbook from Microsoft Forms Quiz Responses

Huda QurayshiHuda Qurayshi Nov 11, 2026 870 views

Question details

The user needs to import Microsoft Forms quiz responses into an Excel workbook and map respondent names, question texts, and scores to an existing layout reliably.

How to Populate an Excel Workbook from Microsoft Forms Quiz Responses
Product
Microsoft Excel
Device & OS
not provided
Scenario
Importing quiz or survey data from Microsoft Forms into a custom Excel layout to analyze scores and responses efficiently.
Observed behavior
Quiz responses require a reliable matching system to map data between respondent identifiers, questions, and respective scores, especially when dealing with live or recurring form submissions.
Before you start

Ensure your Microsoft Forms responses are being synced or exported to an Excel workbook, and identify unique stable identifiers (such as email addresses or student IDs) for accurate data mapping.

Solution 1Recommended

Use Power Query to Import and Transform Forms Data

Power Query provides a robust, automated way to import Forms responses and refresh data seamlessly without manual copying.

If your form is saved on OneDrive or SharePoint, the response workbook updates automatically. Using Power Query allows you to link your custom workbook directly to this live data file, ensuring any new submissions are automatically imported and formatted when you click Refresh.

1
Open your master Excel workbook

Launch the Excel workbook where you have built your custom quiz layout and want the data to populate.

2
Import data via Power Query

Navigate to the Data tab on the ribbon, click 'Get Data', select 'From File', and then 'From Workbook'. Locate your Microsoft Forms response Excel file and click Import.

3
Transform the data

In the Navigator window, select the worksheet containing the form responses and click 'Transform Data'. Here, you can clean up columns, rename headers to match your layout, and set the correct data types.

4
Load into your workbook

Click 'Close & Load' on the Home tab of the Power Query Editor. The response data will now populate into a new sheet, which you can link to your existing layout.

Use Power Query to Import and Transform Forms Data
Tip for Live Updates: Whenever new responses are submitted to the Microsoft Form, simply go to the Data tab and click 'Refresh All' to update your layout automatically.
Free Microsoft Office alternative

Analyze Forms Data Seamlessly with WPS Office

While Microsoft Forms is part of the Microsoft ecosystem, you can effortlessly open, analyze, and map your downloaded response data using WPS Spreadsheet. Enjoy a familiar interface, high compatibility, and powerful lookup functions for free.

  1. 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
  2. 2. Open your exported data: Launch WPS Spreadsheet and open the Excel file you exported from Microsoft Forms.
  3. 3. Apply lookup formulas: Use XLOOKUP or INDEX/MATCH seamlessly to map your response data to your custom layout.
100% compatible with Microsoft Excel formats (.xlsx, .csv)Fully supports XLOOKUP, INDEX/MATCH, and advanced data functionsLightweight, fast, and free to use without a subscription
microsoft office alternative - wps office

Frequently Asked Questions

Why are my Microsoft Forms responses not updating in my Excel workbook?

If you downloaded an offline copy of the responses by clicking 'Open in Excel' from a personal form, it will not sync automatically. To maintain a live connection, the form must be created in OneDrive for Business or SharePoint so it connects directly to a cloud-hosted Excel workbook.

How do I handle multiple responses from the same person in my layout?

You can handle duplicates by using Power Query to sort the responses by submission time and keep only the latest record. Alternatively, you can use the XLOOKUP function and set the search mode argument to '-1' to search from last to first, capturing the respondent's most recent submission.

Can I assign custom scores to text answers during the import process?

Yes. You can create a secondary mapping table in your workbook that assigns numerical point values to specific text answers. Then, use an XLOOKUP or VLOOKUP formula inside your layout to evaluate the text response and output the corresponding numerical score.