Power Query Problems

Excel Power Query: Link Multiple Source Files Reliably

Rana GarciaRana Garcia Nov 12, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Create a file-folder query

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.

2
Select and standardize the tables

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.

3
Load the appended result

Select Home > Close & Load To, choose a table or the Data Model, and load the combined query into the master workbook.

4
Set the refresh behavior

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.

Keep source structures consistent: Do not move or rename the source files after creating the query. If a source table or column changes, update the query steps before refreshing the master workbook.
Free Microsoft Office alternative

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.

Free and lightweight spreadsheet software with a familiar interfaceOpen, edit, and save Microsoft Excel XLSX and XLS filesReduce migration friction when replacing basic Excel workbook workflowsUse compatible spreadsheet formulas, tables, and formatting for shared files
microsoft office alternative - wps office

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.