Excel Power Query: Link Multiple Source Files Reliably
Question details
The user needs a reliable way to update one master workbook from eight Excel source files without maintaining numerous fragile workbook links.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A master workbook must combine data from multiple source files and support repeatable refreshes for ongoing updates.
- Observed behavior
- XLOOKUP links to closed workbooks can cause #REF! errors, broken links, refresh problems, and workbook crashes.
Place the eight source files in a stable shared folder and confirm that their table names, column headers, and data types are consistent. Decide whether the master workbook should refresh when opened or only after a manual review.
Combine the source files with Excel Power Query
Use this approach when multiple workbooks have similar tables and you want one refreshable master query instead of many cell-level links.
Power Query imports and appends the source tables into one result, so the master workbook does not depend on separate XLOOKUP formulas pointing to closed files. The source files should use consistent column names and remain in the same folder or shared location.
Open the master workbook, select the Data tab, choose Get Data > From File > From Folder, browse to the folder containing the source workbooks, and select Combine & Transform Data.
In Power Query Editor, choose the worksheet or Excel table to combine, review the sample file, and use the Transform or Choose Columns commands to keep matching fields and consistent data types.
Select Home > Close & Load To, choose a table or the Data Model, and load the combined query into the master workbook.
Open Data > Queries & Connections, right-click the query, select Properties, and enable Refresh data when opening the file or refresh the query manually with Data > Refresh All.
Reduce broken links by stabilizing the source files
Use these checks when refreshes still fail or when source workbooks are frequently moved, renamed, or edited by multiple users.
Manage Consolidated Spreadsheet Data with WPS Office
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and migrating Excel workbooks. It offers a familiar spreadsheet interface, supports common Excel formats, and can help maintain master workbooks when you are moving away from complex linked-file workflows.

Frequently Asked Questions
Why is Power Query safer than multiple XLOOKUP links to closed workbooks?
Power Query imports and appends the source data into one refreshable result. This reduces dependence on individual file paths and cell references that can break when a workbook is moved, renamed, or unavailable.
How do I refresh the combined source files in Excel?
Open the master workbook and select Data > Refresh All. To automate the process, open Data > Queries & Connections, right-click the query, choose Properties, and enable Refresh data when opening the file.
What causes #REF! errors after linking several Excel files?
Common causes include renamed or moved source workbooks, deleted worksheets or columns, changed table structures, and formulas that reference unavailable cells. Check the source path and query steps before rebuilding links.
Can Power Query combine files with different column names?
It can, but mismatched headers may create missing or null values. Standardize column names and data types before combining, or use Power Query Editor transformations to rename and align the columns.
What should I use instead of linked workbooks for frequent multi-user updates?
Use a database, SharePoint list, or another centralized data source when many users update records. Centralized storage is generally more reliable than maintaining numerous workbook-to-workbook links.




