How to Build an Excel and SharePoint Workflow for Monthly Project Reports
Question details
The user needs to design a robust workflow to collect monthly project data from multiple Excel sheets and consolidate it into a master workbook using SharePoint, Power Automate, and Power Apps.

- Product
- Excel, SharePoint, Power Automate
- Device & OS
- not provided
- Scenario
- Collecting partial data submissions from multiple sheets while accommodating changing project variables and tracking milestone history.
- Observed behavior
- The goal is to maintain a complete history of milestones, phase dates, and delays without overwriting previous master records when different report layouts are submitted.
Define your data submission structure first, deciding exactly which fields must map from individual reports to the normalized master tracking database.
Establish a Normalized Master Database to Preserve History
Ensure project history is tracked correctly by creating a structured SharePoint list or master Excel table where each submission generates a new row.
Instead of overwriting previous records when project information changes, the best practice is to structure your master destination to append new submissions as dated records. This provides a complete history for Power BI to analyze delays and phase changes.
Set up a Master Workbook or SharePoint List containing all required tracking columns (e.g., Submission Date, Project ID, Original Milestone, Revised Date, Delays).
Ensure that incoming Excel data from users is always formatted as a Table (Ctrl + T), which is required for Power Automate to properly identify and extract the rows.
Configure your tracking logic so that each monthly submission creates a completely new row tagged with the current date, rather than updating existing project cells.

Automate Data Collection with Power Automate
Automatically extract data from user-submitted Excel files and append it to your master tracker upon upload.
Implement Power Apps for Controlled Submissions
Restrict how users submit partial sheet data by replacing complex Excel template entry with a structured Power Apps form.
Manage Project Excel Data Effortlessly with WPS Office
While SharePoint and Power Automate workflows require the Microsoft enterprise ecosystem, WPS Office provides a free, highly compatible alternative for organizing, structuring, and consolidating your complex multi-sheet project reports locally.
- 1. Open project templates: Launch WPS Spreadsheet and open your existing .xlsx monthly project reports.
- 2. Standardize inputs: Use the Data Validation feature under the Data tab to restrict what users can enter, preventing layout breaks.
- 3. Consolidate locally: Click 'Consolidate' in the Data tab to manually merge data from multiple regional or departmental sheets into one master view.

Frequently Asked Questions
How do I handle project submissions if users keep changing the Excel layout?
If layouts change frequently, stop relying on direct Excel file extraction. Instead, use a structured input method like Microsoft Forms or Power Apps to feed data directly into your workflow, which forces standard data mapping regardless of their local Excel layout.
How can I track the history of milestone delays over several months?
Ensure your workflow is set to create a new dated record (append) for each monthly submission rather than updating or overwriting existing rows. This allows reporting tools like Power BI to plot changes chronologically by comparing original dates to newer submissions.
Can I extract data from partial sheets using Power Automate?
Yes, but the target data inside the Excel sheet must be formatted as an official Excel Table (Insert > Table). Power Automate relies on named tables to accurately read specific rows and columns, even if the rest of the sheet is unstructured.




