How to Merge Excel Reports from SharePoint Folders into a Dashboard
Question details
The user needs to combine weekly Excel reports stored in a SharePoint folder into a unified, scalable project dashboard without manually downloading the files.

- Product
- Microsoft Excel and SharePoint
- Device & OS
- not provided
- Scenario
- Aggregating multiple weekly status reports from a shared SharePoint repository to create a centralized, real-time project dashboard for team visibility.
- Observed behavior
- Merging is difficult because the source workbooks contain multiple tables, varying row counts, and duplicate records. SharePoint cannot natively merge these files into a dashboard without downloading or using secondary data-processing tools.
Ensure that all weekly Excel reports in your SharePoint folder share a standardized table structure with identical column names and data types before attempting any automated merge operations.
Use Power Query or Power BI to Combine Files from SharePoint
This is the most effective method when local or cloud-based data retrieval is acceptable, allowing you to automatically append changing datasets.
Power Query (built into Excel) and Power BI Desktop natively support connecting to SharePoint folders. This enables you to extract and append multiple weekly workbooks into a single automated dataset, seamlessly handling varying row counts and filtering out duplicate records.
Open Excel or Power BI Desktop. Navigate to the 'Data' tab (in Excel) or 'Home' tab (in Power BI) and select 'Get Data' > 'From File' > 'From SharePoint Folder'.
Enter your root SharePoint site URL (do not include the specific folder path yet). Click 'OK' and sign in using your organizational Microsoft account when prompted.
Click 'Transform Data' to open the Power Query Editor. Filter the 'Folder Path' or 'Name' columns to isolate the specific folder containing your weekly Excel project reports.
Click the 'Combine Files' icon (two downward arrows) located in the header of the 'Content' column. Select the standardized table or sheet name that exists across all your reports to merge them.
Use Power Query features to remove duplicate records or unnecessary columns. Once the data is clean, click 'Close & Load' to output the combined data into your Excel workbook or Power BI data model for dashboard creation.

Implement a Server-Side Dataflow or Custom Web Application
Required if strict policies prohibit downloading or importing workbooks entirely, and a real-time web view is necessary for multiple users.
Manage Your Project Spreadsheets Easily with WPS Office
While establishing automated SharePoint data pipelines requires complex Microsoft server tools, you can handle everyday spreadsheet creation, data cleaning, and local dashboard compilation effortlessly using WPS Office. It serves as a highly compatible, lightweight, and cost-effective alternative to Microsoft Office.

Frequently Asked Questions
Can I merge Excel files directly inside a SharePoint folder without opening them?
SharePoint cannot natively merge data from multiple Excel files just by storing them. You must use data modeling tools like Power Query, Power BI, or set up an automated cloud flow to extract and combine the data into a master report.
How do I handle duplicate records when combining weekly SharePoint reports?
When you use Power Query to combine the files, you can highlight the relevant columns in the Power Query Editor and use the 'Remove Duplicates' feature on the Home tab before loading the final dataset into your dashboard.
Why does changing row counts cause issues when merging spreadsheets manually?
Manual copy-pasting or using strict cell references (like A1:D50) fails when weekly reports have varying row counts, leading to missing data or importing blank rows. Utilizing Excel Tables and Power Query's 'Combine Files' feature dynamically adapts to however many rows are present.
How can I let all team members view the merged dashboard on SharePoint?
The most efficient way is to build the dashboard in Power BI, publish it to a shared Power BI workspace, and then embed it directly onto a modern SharePoint page using the Power BI web part. This gives all authorized users view-only access to the interactive report.




