Data Import & Export

How to Consolidate Microsoft Forms Responses into One SharePoint Excel File

Phi Hung Vo Sep 30, 2026 869 views

Question details

The user wants to consolidate response data from multiple Microsoft Forms into a single Excel workbook located in one SharePoint directory, with a separate worksheet for each form, rather than having data scattered across separate locations.

How to Store Microsoft Forms Responses in One SharePoint Location

Product: Microsoft Forms, SharePoint, Power Automate
Device & OS: not provided
Scenario: Managing data collection from multiple forms and needing a centralized spreadsheet to review all incoming responses.
Observed behavior: Microsoft Forms stores responses for different forms in separate files or locations by default, and lacks a built-in feature to automatically route multiple forms to different worksheets within the same Excel file.

Before you start

Ensure you have an active Microsoft 365 subscription with access to Microsoft Forms, SharePoint, Excel Online, and Power Automate to create the necessary automated workflows.

Solution 1Recommended

Use Power Automate to Route Form Responses to a Centralized Excel File

Create a master Excel workbook in your SharePoint location and set up individual Power Automate flows to push new responses into specific worksheets.

Since there is no built-in automatic consolidation option in Microsoft Forms, you must create an individual Power Automate flow for each form to route the incoming data into your master Excel file. This method ensures real-time updates as responses are submitted.

Create the Master Excel Workbook in SharePoint

Navigate to your desired SharePoint document library. Create a new Excel workbook and add a separate worksheet for each Microsoft Form you want to consolidate. Name the worksheets accordingly (e.g., 'Form 1', 'Form 2').

Format Worksheets as Excel Tables

On each worksheet, create column headers that match the questions on the corresponding Microsoft Form. Select the headers, go to 'Insert', and click 'Table' (ensure 'My table has headers' is checked). Power Automate requires formatted tables to insert data.

Create a New Power Automate Flow

Open Microsoft Power Automate, click 'Create' on the left sidebar, and select 'Automated cloud flow'. Give your flow a name, select 'When a new response is submitted' (Microsoft Forms) as the trigger, and click 'Create'.

Fetch the Form Response Details

In the trigger step, select your specific form from the 'Form Id' dropdown. Click 'New step', search for 'Microsoft Forms', and select 'Get response details'. Select the same Form Id, and click the 'Response Id' field to map it to the dynamic content from the previous step.

Add the Data to the SharePoint Excel Table

Click 'New step', search for 'Excel Online (Business)', and select 'Add a row into a table'. Choose your SharePoint site in 'Location', select your document library, pick the master Excel file you created, and select the specific Table for that form. Map the form response fields to your Excel table columns, save the flow, and repeat this entire process for each of your forms.

Flow Management Requirement: You will need to create, map, and maintain a separate Power Automate flow for every individual Microsoft Form you wish to sync to the master workbook.

Free Microsoft Office alternative

Analyze Your Consolidated SharePoint Data with WPS Office

While you need Microsoft's Power Automate ecosystem to automatically route form responses into SharePoint, WPS Office is an excellent, lightweight alternative for downloading, analyzing, and formatting your final compiled spreadsheet data.

1. Download your SharePoint Excel File: Navigate to your SharePoint document library and download the master Excel workbook containing your consolidated form responses to your local computer.
2. Open in WPS Spreadsheet: Launch WPS Office, select 'Spreadsheets' from the main workspace, and open the downloaded .xlsx file.
3. Analyze the Data: Use WPS Office's built-in filtering, pivot tables, and charting tools to organize and analyze the multi-sheet data effectively.

Fully compatible with Microsoft Excel (.xlsx) files exported from SharePoint.Free, lightweight, and incredibly fast for everyday data analysis.Includes advanced pivot tables, charts, and data visualization tools to analyze form responses.Familiar user interface makes switching from Microsoft Office seamless with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can Microsoft Forms automatically merge responses into one Excel file without Power Automate?

No, Microsoft Forms does not offer a built-in feature to automatically combine responses from multiple distinct forms into a single Excel workbook. You must use Power Automate to route the data or manually copy and paste the responses.

Why is Power Automate unable to find my Excel worksheet or columns?

Power Automate requires the destination data range in your Excel workbook to be formatted as an official Excel Table. If you only type headers into the worksheet without going to Insert > Table, the 'Add a row into a table' action will not recognize the worksheet as a valid data destination.

Do I need a premium Power Automate license to sync Forms to SharePoint Excel?

No. Standard Microsoft 365 business, enterprise, or education accounts typically include the standard permissions required to use both the Microsoft Forms and Excel Online (Business) connectors in Power Automate without needing an additional premium license.

Excel & Data Problems