Others

How to Create a Dynamic Microsoft Form from Excel Data

John Wilson Oct 1, 2026 869 views

Question details

The user needs to generate personalized Microsoft Forms based on individual user records (such as films watched) stored in an Excel list, and consolidate the submitted responses.

How to Create a Dynamic Microsoft Form from Excel Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the distribution of personalized forms to multiple users based on specific data mapped to their email addresses in an Excel spreadsheet.
Observed behavior: Data is currently stored in a central Excel list. A workflow is required to filter this data per user, present relevant choices dynamically via forms, and record the selections back into a consolidated table.

Before you start

Ensure your Excel workbook is saved in Excel Online (OneDrive or SharePoint) and the data range is formatted as a Table. You will also need access to Microsoft Power Automate and Microsoft Forms through an active Microsoft 365 subscription.

Solution 1Recommended

Use Power Automate to Generate Dynamic Forms from Excel Data

Automate the process of filtering Excel data per user, sending customized form options via email, and collecting the responses back into a central Excel file.

This method leverages Power Automate to connect Excel Online and Microsoft Forms. By setting up an automated workflow, you ensure that users only receive data relevant to their specific email address, maintaining privacy and keeping master records secure.

Format Data as a Table

Open your Excel file, highlight your data range containing the records and email addresses, and press Ctrl+T to format it as a Table. Name the table appropriately and ensure the file is saved to OneDrive or SharePoint.

Set Up a Base Microsoft Form

Go to Microsoft Forms and create a base form. Include a choice or text question that will capture the user's selection (e.g., film choices). Keep the form generic, as the specific options will be sent via automation.

Create a Power Automate Flow

Log into Power Automate and create a 'Scheduled cloud flow' or 'Automated cloud flow'. Add the 'List rows present in a table' action from the Excel Online (Business) connector to retrieve your data.

Filter Data by Email Address

Add a 'Filter array' action to your flow to isolate the rows matching individual user email addresses. This ensures each recipient is only matched with their respective records.

Send Personalized Options

Add an action such as 'Send an email (V2)' via the Office 365 Outlook connector. Use dynamic content to insert the user's specific filtered records into the email body along with the link to your Microsoft Form.

Consolidate Responses

Create a separate automated flow starting with the 'When a new response is submitted' trigger from Microsoft Forms. Add the 'Add a row into a table' action for Excel Online to push the submitted selections into a new consolidation table.

Use Power Automate to Generate Dynamic Forms from Excel Data

Data Consolidation: Once your responses are aggregated back into Excel Online, you can easily use Excel PivotTables or Power Query to summarize and analyze the consolidated results.

Free Microsoft Office alternative

Need a simpler way to manage spreadsheet data and surveys?

While Microsoft 365 offers complex automations through Power Automate, WPS Office provides a highly compatible, lightweight alternative. With WPS Spreadsheet and its built-in form capabilities, you can efficiently collect data, manage lists, and analyze responses for free without complicated workflow setups.

1. Download and Install: Download WPS Office for free from the official website and install it on your device.
2. Open Your Excel File: Open your existing .xlsx data files directly in WPS Spreadsheet without losing any formatting.
3. Analyze Results: Use native PivotTables, filters, and charts to instantly organize and consolidate your records.

Seamless compatibility with Microsoft Excel (.xlsx, .xls) files.Built-in data collection tools for easy list management.Free, lightweight application that runs smoothly on any device.Powerful PivotTable and data analysis tools familiar to Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

How do I summarize the submitted Form responses in Excel?

Once responses are saved back to your central Excel table via Power Automate, select the data range and click Insert > PivotTable. This allows you to quickly group, summarize, and analyze the selections (e.g., by genre or user) in a consolidated view.

Can I use Power Query to clean the form responses?

Yes, you can use Power Query (Get & Transform Data) in Excel to connect to the raw responses table, filter out duplicates, merge the data with other lists, and load a clean, automated dataset into your workbook.

How do I ensure GDPR compliance when sending personalized forms?

Keep the master Excel workbook restricted and avoid sharing it directly with respondents. By using Power Automate to filter data server-side by the recipient's email address, the email they receive will only contain their own specific records, preventing data exposure.

Do I need a premium license to connect Excel and Forms in Power Automate?

Standard Microsoft 365 business or education accounts typically include the standard connectors for Excel Online and Microsoft Forms required for this automation. However, interacting with certain external APIs or databases might require a premium license.

Office Quick Solutions