Others

How to Add Form Responses to Multiple Excel Sheets

Ayan Masood Sep 28, 2026 868 views

Question details

The user wants to distribute data from a single submitted survey or ballot so that specific answers are automatically written as new rows into multiple different Excel worksheets within the same workbook.

How to Route One Form's Responses to Multiple Excel Sheets

Product: Excel
Device & OS: not provided
Scenario: Managing survey or ballot data where different categories of responses need to be automatically tracked and separated into distinct worksheets upon submission.
Observed behavior: Currently, form responses go to a single destination, and the user needs an automated workflow to route specific answers to designated worksheets.

Before you start

Ensure that your destination Excel workbook is saved in a cloud location like OneDrive or SharePoint, and that each target range is formatted as a designated Excel Table (Insert > Table).

Solution 1Recommended

Use Power Automate to Route Responses

Create an automated cloud flow that triggers upon form submission to map and distribute answers to separate Excel tables.

Power Automate is the most efficient way to connect Microsoft Forms (or other form tools) directly to Excel. It allows you to set up conditional logic to route specific answers to their respective worksheets automatically.

Create a New Automated Flow

Open Power Automate, click 'Create', and select 'Automated cloud flow'. Choose 'When a new response is submitted' as your trigger and select your specific form.

Retrieve Response Details

Add a new action called 'Get response details'. Select your form again and insert the 'Response Id' dynamic content into the required field.

Add a Row to the First Table

Add a new action called 'Add a row into a table' (Excel Online). Select your cloud storage location, the target Excel file, and the specific Table representing your first worksheet. Map the dynamic form data to the corresponding columns.

Add Rows to Additional Tables

Repeat the 'Add a row into a table' action for the other worksheets in your workbook, ensuring you select the correct target tables and map the relevant answers. Save and test the flow.

Preventing Duplicate Rows: To prevent duplicates, you can use the unique Response ID generated by the form submission to verify if a record already exists before writing a new row.

Solution 2

Manually Filter and Move Responses

If you do not have access to automation tools, you can collect responses in a single master sheet and use built-in filters to distribute the data.

Free Microsoft Office alternative

Manage Your Spreadsheet Data Effectively with WPS Office

While automated cloud routing requires tools like Power Automate, managing, sorting, and analyzing the collected multi-sheet data is incredibly easy with WPS Office. As a powerful, free alternative to Microsoft Office, it handles complex workbooks effortlessly.

1. Download WPS Office: Visit the official WPS website to download and install the free suite.
2. Open Your Workbook: Launch WPS Spreadsheet and open your downloaded form responses.
3. Organize Data: Use the built-in advanced Filter tool to sort responses by category and seamlessly copy them to separate sheets.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in advanced filtering and pivot tables to organize collected responses manually.Lightweight design with a highly familiar tabbed interface.100% free core spreadsheet tools with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Do I need to format my Excel sheets in a specific way for Power Automate to work?

Yes, Power Automate requires your target worksheet ranges to be formatted as Excel Tables. You can do this by highlighting your data headers and pressing Ctrl+T, or navigating to Insert > Table.

Can I conditionally route answers based on what the participant selects?

Yes. In Power Automate, you can add a 'Condition' control after the 'Get response details' step. This allows you to check the value of a specific answer and only execute the 'Add a row into a table' action if the condition is met.

What should I do if my flow fails with a 'Table not found' error?

Ensure that your Excel file is saved in a supported cloud location (like OneDrive for Business or SharePoint). Also, verify that you have explicitly created a Table in the target worksheet, as simply having data on a sheet is not sufficient for the flow to recognize it.

Office Quick Solutions