Sum Cells from SharePoint Workbooks in Excel with Power Query
Question details
Create a master workbook that sums the same cell from multiple Excel workbooks stored on SharePoint.

- Product
- Microsoft Excel Power Query and SharePoint
- Device & OS
- not provided
- Scenario
- The user wants to consolidate values from SharePoint workbooks without opening each source file.
- Observed behavior
- The master workbook should display a total that can be refreshed when source values change.
Make sure you can access the SharePoint site and that the source workbooks use a consistent worksheet and cell location. For easier extraction, consider giving the target cell the same named range in every source workbook.
Combine SharePoint workbook values with Power Query
Use this method to collect a matching value from each workbook and refresh the total without opening the source files individually.
The Combine Files workflow applies the same extraction steps to every selected workbook. A consistently named single-cell range is easier to identify than a cell position in a worksheet preview.
In the master workbook, select Data > Get Data > From Online Services > From SharePoint Folder. Enter the SharePoint site URL, sign in with an account that can access the files, and select Transform Data.
In Power Query Editor, filter Folder Path to the source folder and Extension to the relevant Excel file type. Exclude the master workbook, temporary files, and any other files that should not be totaled.
Select Combine Files on the Content column and choose a representative workbook. In the sample transformation, select the consistently named single-cell range, or select the matching worksheet and extract the target row and column. Check the preview against the source cell before applying the transformation to all files.
Set the extracted value column to a numeric data type, then select Home > Close & Load. In the loaded table, use a SUM formula or insert a PivotTable to total the value column.
Select Data > Refresh All after source workbooks change. To configure refresh behavior, open Data > Queries & Connections, right-click the query, and choose Properties; where available, enable refresh on open or refresh at a specified interval.
Use external references for a small set of workbooks
Use workbook links when there are only a few stable sources and their links can be updated reliably.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Microsoft Office format compatibility helps you move existing files into WPS; use Excel Power Query for the SharePoint consolidation described above.

Frequently Asked Questions
Do I need to open every source workbook to refresh the total?
No. Once the Power Query connection is configured, use Data > Refresh All in the master workbook to retrieve the selected source values without opening each file individually.
How can I make the query find the same cell in every file?
Give the target cell the same single-cell named range in each workbook, then select that range in the Combine Files sample transformation. Alternatively, use a consistent worksheet and verify the target row and column in the Power Query preview.
Will new workbooks in the SharePoint folder be included?
They can be included on the next refresh if they match the query's folder and file filters and follow the same structure as the sample workbook.
Can the total refresh while the master workbook is closed?
Excel's refresh-on-open and interval settings apply when the workbook is open. A separate supported automation or refresh service would be needed to refresh it while closed.




