Route Microsoft Forms Responses to Specific Excel Worksheets
Question details
The user wants to automatically direct Microsoft Forms submissions to different Excel worksheets based on specific responses (like department) and update existing employee records rather than creating duplicate rows.

- Product
- Microsoft Excel, Power Automate
- Device & OS
- not provided
- Scenario
- Automating data collection from Microsoft Forms into categorized Excel tracking sheets without duplicating entries for users who submit updated information.
- Observed behavior
- Seeking a workflow configuration to conditionally route data and dynamically update existing rows based on a unique identifier such as an email address.
Ensure your target Excel workbook is stored in a cloud location like OneDrive for Business or SharePoint Online, and that the data ranges in your worksheets are formatted as official Excel Tables, as Power Automate requires Tables to add or update rows.
Create a Conditional Routing and Update Flow in Power Automate
Set up a Power Automate flow that triggers upon form submission, uses a Switch control to route data to the correct departmental worksheet, and checks for existing records to prevent duplicates.
To achieve conditional routing, you need to use the 'Switch' control in Power Automate, which acts like a branching path based on the user's answer (e.g., Department). To prevent duplicates, you must check the Excel table for an existing unique identifier (like an email address) before deciding whether to add a new row or update an existing one.
Create a new Automated Cloud Flow. Choose the 'When a new response is submitted' trigger and select your Microsoft Form from the dropdown menu.
Add a new action called 'Get response details'. Select the same Form ID and map the Response ID dynamic content to the corresponding field.
Click 'New step' and search for the 'Switch' control. In the 'On' field, select the dynamic content for the question you want to route by (e.g., 'Select your Department'). Create a case for each department name.
Inside each Switch case, add the 'List rows present in a table' action for the corresponding department's Excel worksheet. Use a Filter Query (e.g., Email eq 'responder@email.com') to search for the employee's unique identifier.
Add a 'Condition' below to check if the length of the returned rows is greater than 0. If yes (row exists), add the 'Update a row' action using the row's ID. If no (new employee), add the 'Add a row into a table' action to insert the new form data.

Try WPS Office for Seamless Data Management
While Power Automate workflows are specific to the Microsoft ecosystem, WPS Office provides an excellent, lightweight alternative for handling complex spreadsheet data and creating forms. WPS Spreadsheet is fully compatible with Microsoft Excel, ensuring your tables and data structures remain intact without a heavy subscription fee.
- 1. Download and Install WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open Your Excel Workbooks: Launch WPS Spreadsheet and open your existing .xlsx tracking files. All table formatting and data will seamlessly carry over.
- 3. Explore WPS Forms: Utilize the built-in WPS Forms feature to create custom surveys and easily collect data directly into your spreadsheets.

Frequently Asked Questions
Why is Power Automate not finding my Excel worksheet?
Power Automate requires data to be formatted as an official Excel Table. Open your workbook, highlight your data range, and press Ctrl+T (or Insert > Table) to format it. Once saved to OneDrive or SharePoint, it will appear in Power Automate.
How do I write a Filter Query in Power Automate to find a row?
Filter Queries use OData syntax. To find an email, use the column header name followed by 'eq' (equals) and the dynamic content wrapped in single quotes. For example: EmailColumn eq '[Dynamic_Email_Content]'.
Can I use a personal OneDrive account for this flow?
Yes, but you must use the 'Excel Online (OneDrive)' connector instead of 'Excel Online (Business)'. Note that some advanced actions might vary slightly between the personal and business connectors.
What happens if a user submits a form without a department selected?
If you are using a Switch control and the response does not match any of your defined cases, the flow will route to the 'Default' case. You should configure the Default case to either send an error email or add the row to an unassigned holding worksheet.




