Others

How to Automate Weekly Excel Updates, Consolidation, and Reminders

Aamir Naveed AkramAamir Naveed Akram Nov 3, 2026 869 views

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.

How to Automate Weekly Excel Updates, Consolidation, and Reminders
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a standardized data collection form

Open Microsoft Forms and create a new form with fields for Team Name, Department, and the weekly topic updates you need to collect.

2
Set up a SharePoint list or master Excel file

Create a single master Excel workbook on SharePoint. Insert a table (Insert > Table) with columns matching your form questions to store the incoming data.

3
Automate data consolidation

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.

4
Create a scheduled flow for reminders

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.

5
Send automated emails for missing submissions

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.

Automate Data Collection and Reminders with Microsoft Forms and Power Automate
Power BI Integration: Once your data is flowing automatically into a central SharePoint Excel file or List, you can connect it directly to Power BI for real-time dashboard reporting.
Free Microsoft Office alternative

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. 1. Download and install WPS Office: Visit the official WPS website and download the free desktop application for your operating system.
  2. 2. Open your existing Excel files: Launch WPS Spreadsheet and open your .xlsx department workbooks directly with full formatting preservation.
  3. 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.
100% compatible with Microsoft Excel (.xlsx, .xls) formats and formulasBuilt-in Data Consolidate feature for merging multiple worksheets effortlesslyLightweight application that runs smoothly on all devices without laggingFree to download with a familiar tabbed user interface
microsoft office alternative - wps office

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.