Power Query to Combine Excel Files: A Practical Guide

To combine multiple Excel files in Microsoft 365 for Windows, put them in one folder, then select Data > Get Data > From File > From Folder. Choose the folder, select Combine & Transform Data, pick the sheet or table to use, and load the result; after that, refresh the query to bring in files added to the folder.

What Power Query does when it combines files

Power Query is Excel’s built-in tool for importing, cleaning, and reshaping data. When you combine files from a folder, it reads the contents of each eligible file, applies a set of transformation steps, and appends the resulting rows into one table. This is useful for recurring collections such as weekly sales reports, monthly expense workbooks, or survey exports.

For example, suppose a folder contains January, February, and March workbooks, each with a table named Sales and the columns Date, Region, and Amount. Power Query can stack those table rows into one result. You can then create a PivotTable, chart, or formula-based report from the consolidated data. When April’s workbook is added, refreshing the query can include its rows without repeating the consolidation manually.

Combining files is generally an append operation: it adds records from one file below records from another. It is not the same as merging two tables by a matching key, such as matching each sales row to a product lookup table. Power Query has separate append and merge operations for those different jobs.

Prepare the folder and workbooks

The combine process is most reliable when the files share a consistent layout. Power Query uses one file as a sample to determine how to transform the others, so differences in sheet names, headers, or data placement can produce missing or misaligned results.

Keep the same kind of data in each file, with the same column names and broadly consistent column meanings.
Put headers in the same row and keep the relevant data in the same Excel table or worksheet in every workbook.
Prefer formatted Excel tables over loose ranges. Give the tables a consistent name, such as Sales, in each workbook.
Keep unrelated workbooks, old exports, and the final output workbook out of the source folder, or filter them out in Power Query.
Use a stable folder location. Moving or renaming the folder can break the query’s source path.

Files do not have to contain identical numbers of rows. They do need to expose the data in a way that the sample-file transformation can handle. A workbook with a title block above the headers, a renamed sheet, or a different table name may need extra cleanup or a separate query. Avoid merged cells and decorative report layouts in source data when possible; they are designed for presentation, not reliable row-by-row import.

Combine files step by step

Save or copy the source workbooks into one folder. If you are combining files stored on a network or cloud location, confirm that the location is accessible from the computer that will refresh the query.
In Excel for Windows, open the workbook that will hold the combined result. Select Data > Get Data > From File > From Folder.
Browse to the source folder and select Open. Review the file list. If it contains unrelated files, you can cancel and tidy the folder, or continue and filter the list during transformation.
Select Combine & Transform Data to inspect and shape the data before loading it. Combine & Load is a quicker route when the files are already uniform and the default choices are suitable.
In the Combine Files dialog, choose the worksheet or table that contains the records. The preview is based on a sample file. Check that it shows the right headers and data, then confirm.
In the Power Query Editor, review the combined rows and applied steps. Remove unwanted columns, correct data types, filter out non-data rows, or keep the source filename if it will help identify where a row came from.
Select Home > Close & Load to load the result into a worksheet. To choose a different destination or load the query as a connection, use Home > Close & Load To.

Excel creates supporting queries and transformation steps behind the combined result. The exact names can vary, but the setup commonly includes a folder listing, a sample-file transformation, and a main query that applies the transformation across the files. You can inspect these in Data > Queries & Connections and edit a query by opening it in Power Query Editor.

How refresh works and what it does not do

The output table is a query result, not a live link that recalculates every time a source workbook changes. To update it, select Data > Refresh All, or right-click the query’s output table and select Refresh. Refresh makes Power Query read the source location again and apply the saved steps. Depending on the workbook and data source, refresh settings can also be configured in the query’s properties.

When the query is based on a folder, files that meet its filters may be included on a later refresh. This makes the method useful for a recurring process: save the next period’s workbook in the folder, then refresh. It does not automatically copy files into the folder, repair a changed source layout, or guarantee that a newly added file will pass the query’s filters.

If the combined output is loaded to a worksheet, treat it as generated data. Avoid typing corrections directly into the result table because a refresh can replace or rearrange the rows. Make lasting fixes in the source files or as transformation steps in the query. Keep manual notes in a separate table and connect them by a stable identifier if they need to accompany the imported data.

Filtering files and keeping their names

A folder may contain temporary files, backups, or workbooks that should not be included. In the folder listing, useful fields can include Name, Extension, Folder Path, and file attributes. Filter the listing before the combine step so only the intended files are transformed. For example, filtering the extension to .xlsx can exclude a text export, while filtering the name can exclude an archive whose filename starts with “Old”. Check the actual file list and filter logic rather than assuming every file in the folder is appropriate.

Power Query may also encounter Excel lock files whose names begin with a tilde and dollar sign while a workbook is open. If one appears in the listing or causes a refresh issue, exclude it with a filename filter. A filter on the folder path can also help when the source folder contains subfolders or when the connector lists files below the selected location. Review the preview after filtering to confirm that the intended set remains.

The combined query can retain the source filename as a column. This is valuable for tracing an unexpected value back to its original workbook or identifying which reporting period supplied a row. Keep that column when you need auditability; remove it only if it is unnecessary in the final analysis.

Common problems and practical fixes

Columns are missing or shifted: Compare the headers and layout in the sample file with the other workbooks. Standardize the source files, or revise the query so it handles the variation explicitly.
Headers appear as data: The source may have extra title rows, or the query may not be promoting the correct row to headers. Remove the preamble and promote the proper row in Power Query Editor.
Numbers or dates look wrong: Check the column’s data type and regional interpretation. A date written with day and month in an ambiguous order may be interpreted differently by locale. In the query, use the appropriate data type and locale where needed.
Some files do not appear: Review extension, filename, folder-path, and hidden-file filters. Also confirm that the file is in the folder the query actually uses and is a supported, readable workbook.
Refresh reports that a file cannot be accessed: Check whether the workbook was moved, renamed, deleted, or is unavailable due to permissions or a network connection. Restore access or update the source path.
Repeated rows appear: Power Query is appending what it finds; it does not automatically know which records are duplicates. Decide which columns define a duplicate, then use a suitable remove-duplicates step or handle duplicates in the analysis.

Data types deserve particular attention. A column that contains numeric amounts in most files but a note such as “Pending” in one file may be interpreted as text or produce conversion errors, depending on the transformation. Decide whether those notes should be removed, preserved as text, or corrected in the source. Do not simply dismiss errors if they represent missing or misread business data.

Append versus merge, and other ways to consolidate

Use the folder-combine workflow when each file contributes more rows to the same dataset. For instance, append regional files if each has the same sales columns and each row is a transaction. Use Power Query’s merge operation when you need to bring columns from a related table into another table by matching values, such as adding product descriptions using a product ID. These operations solve different problems and can be used together in a larger query.

Copying and pasting is reasonable for a one-time consolidation of a few small files, but it is easy to omit rows or paste data into the wrong columns. Excel’s Data > Consolidate feature is intended for summarizing data from ranges, not for the same repeatable row-stacking workflow. A VBA macro can be tailored to special workbook layouts, but it requires code maintenance and more careful handling. Power Query is usually a practical middle ground when the data is structured and the task recurs.

Excel for Mac and Excel for the web

The instructions above describe Excel for Microsoft 365 on Windows. Power Query is available in Excel for Mac, but its connectors and authoring options can differ by Excel build and update channel. Look under Data > Get Data for the available sources; if From Folder is not offered in your installation, use a supported Windows version to create the folder query or choose another consolidation method. A workbook’s ability to refresh can also depend on the source connector and access permissions.

Excel for the web is not a full replacement for the Windows Power Query authoring experience. Some query refresh scenarios are supported, but available editing and source capabilities vary. If you need to create or troubleshoot a folder-based query, desktop Excel for Windows is the most straightforward environment for the steps described here. Confirm that the final workbook can refresh in the environment where it will be maintained.

Frequently Asked Questions

Can Power Query combine Excel files with different names?
Yes. The workbooks can have different filenames. What matters most is whether their contents match the transformation’s expectations. You can keep the filename in the output as a source column for tracking.

Do all files need the same number of rows?
No. Each file can contribute a different number of records. Consistent column structure, headers, and data meaning are more important than matching row counts.

Will Power Query include new files automatically?
It can include eligible files found in the source folder when the query is refreshed. It does not update the result immediately when a file is saved, and filters or structural differences can prevent a new file from being included correctly.

Can I combine multiple sheets from each workbook?
The standard Combine Files setup applies a transformation based on a chosen sample object, such as a sheet or table. If you need several sheets from every workbook, the query usually needs additional shaping so it selects and appends the intended sheets rather than just one object.

Can I combine files stored in separate folders?
The simplest setup points to one folder. You can sometimes use a common parent folder and filter by Folder Path, or create separate folder queries and append their results. The right option depends on whether the folder structure and workbook layouts are consistent.

Can Power Query combine CSV files as well as Excel workbooks?
Yes, Power Query can import and combine supported text files, including CSV files, using a folder-based workflow. Keep the file formats consistent or filter the folder listing so the query does not try to process unrelated file types with the same transformation.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.