SharePoint Document Issues

How to Merge Excel Reports from SharePoint Folders into a Dashboard

Algirdas JasaitisAlgirdas Jasaitis Oct 22, 2026 869 views

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.

How to Merge Excel Reports from SharePoint Folders into a Dashboard
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.
Before you start

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.

Solution 1Recommended

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.

1
Connect to the SharePoint folder

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'.

2
Authenticate and enter the URL

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.

3
Transform and filter data

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.

4
Combine the workbooks

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.

5
Clean data and load to dashboard

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.

Use Power Query or Power BI to Combine Files from SharePoint
Scheduled Refresh: If using Power BI, you can publish the dashboard to the Power BI Service and configure a scheduled refresh so the dashboard automatically updates as new Excel files are added to SharePoint.
Free Microsoft Office alternative

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.

Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV file formatsFree and lightweight office suite for Windows, Mac, Linux, and mobile devicesFamiliar user interface ensuring seamless migration and zero learning curvePowerful built-in formulas, pivot tables, and charting tools for customized local dashboards
QA img-9

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.