How to Populate an Excel Workbook from Microsoft Forms Quiz Responses
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.

- 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.
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.
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.
Launch the Excel workbook where you have built your custom quiz layout and want the data to populate.
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.
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.
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.

Retrieve Specific Answers Using XLOOKUP or INDEX/MATCH
Ideal for extracting specific scores or responses into a custom-designed grading sheet using powerful lookup formulas.
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. Install WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open your exported data: Launch WPS Spreadsheet and open the Excel file you exported from Microsoft Forms.
- 3. Apply lookup formulas: Use XLOOKUP or INDEX/MATCH seamlessly to map your response data to your custom layout.

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.




