How to Automatically Update an Excel Chart with Microsoft Forms Responses
Question details
The user wants to automatically update an Excel chart using new data submitted through Microsoft Forms via Power Automate.
- Product
- Microsoft Excel, Microsoft Forms, Power Automate
- Device & OS
- not provided
- Scenario
- Automating data collection visualization by feeding form responses directly into a continuously updating spreadsheet chart.
- Observed behavior
- The goal is to establish a seamless workflow where new form responses automatically populate a SharePoint-hosted Excel workbook, instantly updating the linked charts.
Ensure you have an active Microsoft 365 subscription with access to Microsoft Forms, Power Automate, and SharePoint or OneDrive for Business.
Configure Power Automate to Update an Excel Table
Set up a flow that captures form responses and adds them to an Excel table, allowing connected charts to update dynamically.
To make an Excel chart update automatically, the data source must be formatted as an official Excel Table. This ensures that as Power Automate pushes new rows into the file, the chart's data range automatically expands.
Create a blank Excel workbook in SharePoint. Set up your column headers to match your Form questions, select them, and press Ctrl+T (or go to Insert > Table) to format them as a Table.
Log into Power Automate, click 'Create', and select 'Automated cloud flow'. Choose 'When a new response is submitted' under Microsoft Forms as your trigger.
Add a new step for 'Get response details' (Microsoft Forms). Select your Form ID and map the Response ID from the dynamic content.
Add another step using the 'Add a row into a table' action for Excel Online. Select your SharePoint site, Document Library, File, and the Table you created. Map the dynamic form data to your table columns and save the flow.
Open your Excel workbook in SharePoint, highlight your Table, and go to Insert > Chart. As new form responses trigger the flow, rows will be added, and your chart will update automatically.
Seek Advanced Help in the Power Automate Community
If your flow requires complex data transformations or you encounter authorization errors, consult the official community for specialized assistance.
Try WPS Office for Dynamic Charting and Spreadsheets
While automated cloud flows require specific Microsoft integrations, WPS Office provides a lightweight, highly compatible, and free alternative for all your everyday spreadsheet and charting needs. Enjoy a familiar interface that makes migrating from Microsoft Excel seamless.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free suite and follow the simple installation instructions.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing .xlsx files without worrying about format distortion.
- 3. Create Dynamic Charts: Highlight your data and use the rich built-in chart options to visualize your metrics effortlessly.
Frequently Asked Questions
Why is my Excel chart not updating when new form responses are added?
Ensure your chart's data source is a formatted Excel Table (Insert > Table) rather than a static range. Formatted tables automatically expand to include new rows added by Power Automate, which prompts the chart to update.
Can I use OneDrive instead of SharePoint for this automation?
Yes. In Power Automate, you can use the 'Excel Online (Business)' or 'Excel Online (OneDrive)' connector to add rows to a workbook saved directly in your personal or business OneDrive account.
Is a premium Power Automate account required for this integration?
No, the standard connectors for Microsoft Forms and Excel Online are included in most basic Microsoft 365 business and educational subscriptions, allowing you to build this flow without premium licensing.
