iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To combine similarly structured Excel files, place them in one folder and use Power Query’s From Folder connector. It builds a repeatable query that can be refreshed when the source files change. In Power Query, append stacks rows; merge joins tables using matching values. The folder workflow is usually the right starting point when your goal is to combine several workbooks into one table.
Prepare the workbooks and folder
Create a dedicated folder containing only the files you want to combine. The folder connector can include files in the selected folder and its subfolders, so keep unrelated files and nested folders out of the selection or filter them out in Power Query.
For the smoothest combination, use files with the same schema: consistent column headers, data types, and number of columns. Column order can differ because Power Query matches columns by name when it appends data. Microsoft’s guidance is to keep headers, types, and column counts consistent: Import data from a folder with multiple files (Power Query).
If a workbook contains multiple sheets, tables, or named ranges, decide which object holds the data you need. The sample file and object selected during setup determine the pattern Power Query uses for the other files, so choose a representative workbook rather than one with an unusual layout.
Combine files from a folder
- In Excel, select Data > Get Data > From File > From Folder, then browse to the folder containing the workbooks.
- Review the file list to confirm it includes the intended files. If you need tighter control, choose Transform Data, filter the listing—for example, by Extension or Folder Path—and select the Content column.
- Choose a Combine option. From the Power Query window, use Home > Combine Files. For Excel workbooks, select the sample file and the relevant worksheet, table, or named range in the Combine Files dialog. The dialog uses one sample object; it cannot use multiple selected objects as the sample.
- 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 decide whether the option to skip files with errors is appropriate for your data.
- Apply any needed transformations, then choose Close & Load or use Load To to select a destination. Excel supports loading to a worksheet or the Data Model.
Microsoft documents the folder import and combine workflow, including selecting a sample object and the load options, in its folder import instructions.
Choose append or merge for the result you need
People often use “merge” to mean any kind of combining, but Power Query has two distinct operations. Choose based on whether you need to add records or match related information.
Rank #2
| Your goal | Operation | Result |
|---|---|---|
| Stack monthly, department, or other similarly structured records into one longer table | Append | Adds the rows from one query after another. Columns align by header name, not position; a column missing from one query receives nulls for its rows. |
| Add related fields to existing records, such as product details to sales rows | Merge | Joins two queries using one or more matching column values. The matching table appears as a structured column; expand it to choose which fields to add. |
Append can combine tables from different sources. Merge also supports queries from different external sources, but it requires at least two queries and at least one column to match. For the details, see Microsoft’s guidance on appending queries and merging queries.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Transform, load, and refresh the combined query
Power Query records transformations as applied steps. After loading the result, refresh the query to rerun those steps against the connected data when source files change. The folder-based query therefore avoids repeating the copy-and-paste work, provided the files remain accessible and continue to follow the expected structure.
Load the final query to a worksheet when you want a visible table in the workbook, or to the Data Model when you need it as part of a relational workbook model. Microsoft explains Power Query’s role and its Excel loading and refresh capabilities in About Power Query in Excel.
Protect data when combining sources
If the query combines sensitive information or sources with different privacy classifications, review Power Query privacy levels. Microsoft says these levels are intended to prevent inadvertent combinations that could expose data from private or organizational sources to another source. See Microsoft’s documentation for merge queries and append queries.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Check your Excel version and platform
Microsoft lists folder import support for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Its merge and append instructions list Microsoft 365, Excel 2024, and Excel 2021, with Mac listed for those operations. Power Query support on Mac and the web varies by source and version, so the same ribbon path or feature availability should not be assumed across every edition. Check your Excel ribbon and version-specific Power Query help if a command is missing.
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 →Quick Recap
Best Value
- Guide students toward a healthy lifestyle, both physically and financially
- This revised and expanded edition adds much more information on work ethic, nutrition, and exercise; updates the sections on sexually transmitted diseases and drugs; and includes completely new sections on preparing financially for the future
- Graphic organizers, self inventories, puzzles, real-life situations, and cloze activities provide creative opportunities for students to assess their own lifestyles and make good choices for the future
- Prepare students for adulthood
- Practical lessons to help handle real life events
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.
