Power Query Problems

Sum Cells from SharePoint Workbooks in Excel with Power Query

Huma Ashraf ChHuma Ashraf Ch Nov 20, 2026 869 views

Question details

Create a master workbook that sums the same cell from multiple Excel workbooks stored on SharePoint.

Sum Cells from SharePoint Workbooks in Excel with Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Connect to the SharePoint site

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.

2
Filter the source files

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.

3
Combine the matching values

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.

4
Load and total the results

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.

5
Refresh the master workbook

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.

Refresh timing: The total reflects the last successful refresh, not necessarily live changes. Refresh on open or at intervals requires the workbook to be open; those settings do not refresh a closed workbook.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS filesReview consolidated values and calculate spreadsheet totalsUse a familiar spreadsheet interface for everyday workMove existing Office files into a lightweight office suite
microsoft office alternative - wps office

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.