Data Import & Export

How to Assign a Unique ID to Microsoft Forms Responses in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to automatically generate and assign a unique identifier to every new response submitted via Microsoft Forms when it populates an Excel spreadsheet.

Product: Microsoft Excel, Power Automate
Device & OS: not provided
Scenario: Automating data collection from Microsoft Forms into an Excel workbook while ensuring each entry has a distinct, static tracking ID.
Observed behavior: Currently, form responses populate the Excel file but do not have a custom unique ID automatically generated in the designated row upon submission.

Before you start

Ensure your target Excel workbook is stored in a cloud location like OneDrive for Business or SharePoint, formatted as an official Excel Table, and that you have an active Microsoft Power Automate account.

Solution 1Recommended

Use Power Automate to Generate and Assign Unique IDs

Create a flow in Power Automate that triggers on a new Forms submission, generates a unique identifier, and adds the data into a corresponding Excel row.

Since Microsoft Forms and Excel do not natively generate robust custom unique IDs upon submission without automation, Power Automate bridges this gap. By setting up a cloud flow, you can pull the exact data from the form and inject an ID (either the default Response ID or a generated GUID) directly into your Excel table.

Create a New Automated Flow

Log in to Power Automate, click on 'Create', and select 'Automated cloud flow'. Name your flow and choose 'When a new response is submitted' (Microsoft Forms) as your trigger.

Get Response Details

Add a new step and search for 'Get response details'. Select your Form ID from the dropdown menu, and click on the Response ID field to add the dynamic content 'Response Id' from the trigger.

Add a Row into an Excel Table

Add another step and search for 'Add a row into a table' (Excel Online). Select the Location, Document Library, File, and Table where your data resides.

Map Fields and Assign the Unique ID

Once the table is selected, the column names will appear. Map your form questions to the relevant columns. In the column designated for your Unique ID, insert the 'Response Id' dynamic content or use the expression `guid()` to generate a completely random unique identifier. Save and test your flow.

Community Support: If your scenario requires more complex conditional logic or unique ID formatting, the Microsoft Power Platform Community is an excellent resource for designing custom Forms-to-Excel flow configurations.

Free Microsoft Office alternative

Analyze and Manage Your Spreadsheet Data with WPS Office

While automated workflows like Power Automate are specific to the Microsoft ecosystem, once your data is exported and saved, you can seamlessly open, edit, and analyze the resulting Excel files using WPS Office. It provides a lightweight, robust, and free alternative to manage your daily spreadsheet tasks without expensive subscriptions.

1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and install it on your device.
2. Open Your Exported Data: Launch WPS Spreadsheet and open the .xlsx file where your form responses and unique IDs were saved.
3. Analyze Your Responses: Use WPS Spreadsheet's built-in filtering and pivot table tools to organize, track, and analyze your collected form entries.

100% compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightweight application ensures fast loading and smooth scrolling of large datasets.Familiar ribbon interface means no steep learning curve.Built-in advanced filtering, pivot tables, and data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I just use an Excel formula like =ROW() to generate unique IDs?

While formulas like =ROW() can generate sequential numbers, they are dynamic. If you sort, filter, or delete rows in your Excel table, the numbers will change or recalculate, which ruins data consistency. A static ID generated via Power Automate is much more reliable for form submissions.

Why isn't my Excel file appearing in the Power Automate dropdown?

For Power Automate to detect and interact with your Excel file, the file must be saved in a cloud location (like OneDrive for Business or SharePoint). Additionally, the data range inside the workbook must be formatted as an official Excel Table (Insert > Table).

Can I customize the format of the Unique ID, like 'ID-001'?

Yes. In Power Automate, when mapping the Unique ID field in the 'Add a row into a table' step, you can type static text alongside dynamic content. For example, you can type 'ID-' and then insert the 'Response Id' dynamic tag right next to it.

Excel & Data Problems