To combine similarly structured Excel files, place them in one folder and use Power Query’s From Folder connector. It creates a repeatable query that can stack the files’ records and refresh when the source data changes. In Power Query, that operation is called append; merge means joining tables using matching values, such as a product ID.
Choose the right operation: append or merge
People often use “merge” to mean combining files, but Power Query distinguishes two results. Use append to put similar records into one longer table. Use merge to bring related fields from one table into another using a shared key.
As an Amazon Associate I earn from qualifying purchases.
| Your goal | Power Query operation | Result |
|---|---|---|
| Stack monthly or departmental records | Append | Adds rows from one query after another. Columns are matched by header name, not position; columns without a match receive null values. |
| Add related details, such as product information to sales records | Merge | Joins queries on one or more matching column values. The matched data appears in a structured column that you can expand to select fields. |
For multiple similarly structured workbooks, start with the folder workflow below. It uses the combine-files process to bring their records together. If you already have separate queries and want to join related tables, see Microsoft’s guide to merging queries and guide to appending queries.
Prepare the workbooks and folder
- Create a dedicated folder containing only the workbooks you want to include. The folder connector can include files in the selected folder and its subfolders, so keep unrelated files and subfolders out of the selection.
- Keep the source data consistent. Microsoft recommends consistent column headers, data types, and numbers of columns. Column order can differ because append aligns columns by name.
- Check that each workbook contains the intended worksheet, table, or named range. If workbooks contain multiple objects, you will choose the relevant object during setup.
Microsoft’s folder-import guidance explains the supported combine process. The sample file and object you choose define the pattern Power Query uses for the files, so select one that represents the data shape you want.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Combine the files from a folder
- In Excel, select Data > Get Data > From File > From Folder, then browse to the folder containing your workbooks.
- Review the file listing and confirm it contains the intended files. If necessary, choose Transform Data to inspect and filter the listing—for example, by extension or folder path.
- For a straightforward setup, choose a Combine option. For more control, select the Content column in the folder listing, then choose Home > Combine Files.
- In the Combine Files dialog, select a sample file and the worksheet, table, or named range containing the data. A workbook can contain multiple objects, but the dialog uses one selected object from one sample file to build the query.
- Choose Combine and Transform Data to inspect and shape the result before loading, or Combine and Load to create and load the query directly. Review the preview and the option for skipping files with errors before continuing.
- Apply any needed transformations, then select Close & Load or choose Load To to select a destination.
Microsoft documents loading the result to a worksheet or the Data Model. For screenshots and details of the folder workflow, consult its Power Query folder-import instructions.
Shape the result and refresh it later
Use the Power Query editor to review the combined rows, adjust data types, rename or remove columns, and make other needed changes before loading. Power Query records these transformations as applied steps. When the source data changes, refreshing the query runs those steps again against the connected data; it does not require manually repeating the consolidation.
Load to a worksheet when you want a visible table, or to the Data Model when you are building a relational workbook model. Microsoft’s overview of Power Query in Excel describes its data-connection and transformation workflow.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Check privacy when combining sources
When the query combines data from sources with different privacy classifications, review the sources’ privacy levels. Microsoft says these settings are intended to prevent inadvertent sharing of information from private or organizational sources with another source. See Microsoft’s guidance on merging queries and privacy levels.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Check your Excel version and platform
Microsoft lists the folder-import feature for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Its merge and append documentation lists Microsoft 365, Excel 2024, and Excel 2021, including Mac. Power Query support on Mac and the web varies by source and version, and interface paths may differ. Check the ribbon and version-specific Power Query for Excel help if the labels above do not appear in your edition.
Quick Recap
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
