How to Automate Weekly Excel Updates, Consolidation, and Reminders
Question details
The user needs to collect weekly updates from multiple department managers, consolidate the data into a single report or email, and automatically track and follow up on missing submissions.

- Product
- Excel, Power Automate, SharePoint
- Device & OS
- not provided
- Scenario
- Managing weekly data collection from roughly 40 teams across 4 workbooks, requiring a streamlined workflow to consolidate the updates and remind managers who fail to submit on time.
- Observed behavior
- Currently, data is collected and consolidated manually by department heads across separate workbooks, and tracking down missing updates requires manual follow-ups.
Ensure you have an active Microsoft 365 subscription with access to Power Automate, Microsoft Forms, and SharePoint or OneDrive for Business, as these tools are required to build the automated workflow.
Automate Data Collection and Reminders with Microsoft Forms and Power Automate
This is the most robust method for automating data collection. It replaces manual workbook entries with a standardized form, automatically saving responses to SharePoint and triggering scheduled reminders for missing submissions.
By shifting data entry to Microsoft Forms, you eliminate the need to manually consolidate multiple Excel files. Power Automate can handle the background logic to compile the answers and identify who hasn't submitted their weekly report.
Open Microsoft Forms and create a new form with fields for Team Name, Department, and the weekly topic updates you need to collect.
Create a single master Excel workbook on SharePoint. Insert a table (Insert > Table) with columns matching your form questions to store the incoming data.
In Power Automate, create an automated cloud flow triggered by 'When a new response is submitted'. Add the 'Add a row into a table' Excel Online action to instantly write form responses into your master SharePoint Excel file.
Create a 'Scheduled cloud flow' in Power Automate set to run on your weekly deadline. Use the 'List rows present in a table' action to read the master Excel file and compare the submitted teams against a master list of all teams.
Add a condition in your flow to filter out teams that have already submitted. Use the 'Send an email (V2)' action via Office 365 Outlook to automatically email the managers of the missing teams.

Manually Consolidate Multiple Worksheets in Excel
If you prefer to keep your existing multi-workbook structure instead of using Forms and Power Automate, you can use Excel's built-in consolidation feature.
Consolidate Spreadsheets Easily with WPS Office
While advanced background automation relies on specific ecosystem tools, WPS Office provides a highly compatible, lightweight, and free alternative to Microsoft Excel for managing, sharing, and consolidating complex spreadsheets.
- 1. Download and install WPS Office: Visit the official WPS website and download the free desktop application for your operating system.
- 2. Open your existing Excel files: Launch WPS Spreadsheet and open your .xlsx department workbooks directly with full formatting preservation.
- 3. Consolidate data seamlessly: Go to the Data tab and use the intuitive Consolidate feature to merge your department sheets into a single weekly report.

Frequently Asked Questions
Can I consolidate Excel sheets without using Power Automate?
Yes, you can use the built-in 'Consolidate' feature under the Data tab in Excel or WPS Spreadsheet. Alternatively, you can use Power Query to pull and merge data from multiple workbooks stored in a single folder manually.
How do I send automatic emails directly from an Excel workbook?
Excel itself cannot send scheduled emails automatically out of the box. You must either write a custom VBA (Visual Basic for Applications) macro connected to Outlook or use Power Automate to read the file and trigger the emails.
Is SharePoint required for Microsoft Power Automate flows?
While not strictly required for all flows, storing your master Excel file on SharePoint or OneDrive for Business is highly recommended. Power Automate needs the file to be stored in a supported Microsoft cloud location to read, update, and automate data reliably.




