Use Power Query to Merge Excel Files: A Practical Guide

How-to basics · Updated November 20, 2026

In Excel for Microsoft 365 on Windows, go to Data > Get Data > From File > From Folder, choose the folder, and select Combine & Transform Data. Power Query can append the rows from similarly structured workbooks into one refreshable result, so you do not have to copy and paste each file by hand.

What Power Query means by combining files

Power Query is Excel’s data-import and transformation tool. When you use its folder workflow, it reads files from a chosen location, applies a consistent set of steps to their contents, and brings the resulting rows together in one query. The source workbooks remain unchanged; the combined result is a separate query that you can load to a worksheet or, when appropriate, the Data Model.

The folder workflow is often described casually as merging files, but Power Query’s terminology matters. Its Combine Files command generally appends matching tables: it stacks the rows from one file under the rows from another. For example, four monthly sales workbooks with the columns Date, Region, Product, and Sales can become one table with all four months of records.

Power Query can also perform a true merge: joining two tables using a matching field, such as a product ID. That is a different operation from combining files in a folder. Knowing which result you need helps prevent a common mistake: treating a row-by-row append as if it matched records between two datasets.

Prepare the folder and source workbooks

Before importing, make the files as consistent as possible. A reliable set of workbooks has the same kind of data in the same named worksheet or Excel table, with compatible column headings and data types. The files do not have to contain the same number of rows, but a column called Sales in one file should not mean something different from Sales in another.

  • Put the intended source files in one folder. Keep unrelated workbooks and the final output workbook somewhere else, or filter them out during import.
  • Use a consistent layout: one header row, no title or notes above the data, and no subtotal rows mixed into the records.
  • Where possible, format each source range as an Excel table and give the tables a consistent structure. Tables are often easier to identify than loosely arranged worksheet ranges.
  • Check that dates, numbers, and identifiers are stored consistently. An ID such as 00127 should not be treated as a number in one file and text in another if its leading zeros matter.
  • Use one file type when practical. If a folder contains different formats, filter the file list by Extension and confirm that the selected files are all suitable for the same combine process.

Power Query uses a sample file to determine how to interpret the contents of the folder. If one workbook has a different worksheet name, extra introductory rows, or an unusual layout, the sample-based steps may not work correctly for every file. A quick review of the source structure can save considerable cleanup later.

Combine files from a folder step by step

  1. Open the workbook that will hold the result. In Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Folder.
  2. Browse to the folder containing the source files and select it. The file list lets you inspect what Power Query found. If the folder contains extra files, choose Transform Data so you can filter the list before combining.
  3. Choose Combine > Combine & Transform Data. In the combine dialog, select the worksheet, table, or other object that contains the records. Check the preview to ensure it shows the expected headings and rows, then confirm.
  4. Power Query opens the combined data in the Power Query Editor. Review the column names, sample rows, and applied steps. Remove unwanted columns or rows and correct any types or headers that were interpreted incorrectly.
  5. When the result looks right, choose Home > Close & Load to send it to a worksheet. Use Home > Close & Load To if you want to specify a destination or load option.

The folder import creates supporting queries and steps that describe how to process each file, not just a one-time pasted result. The editor may show a main combined query along with helper queries such as a sample-file query. These components are part of the combine process; avoid deleting them unless you understand how the main query depends on them.

Check the sample file and transform the result

The combine dialog’s sample selection is important when each workbook contains multiple sheets or tables. Choose the object that represents the repeated data in every workbook. If the data is consistently stored in a table called SalesData, select that table rather than a worksheet that also contains instructions, totals, or unrelated content.

After combining, inspect several rows from different source files if possible. Common adjustments include promoting the correct row to headers, removing blank rows, filtering out report titles, changing a column from text to date, and excluding a totals row. Power Query records those actions as steps, so the same transformations can be applied again when the query refreshes.

Be cautious with automatic data type detection. A column that looks numeric in the sample may contain a text value or a blank in another workbook. Identifiers, postal codes, and account codes are often better kept as text because arithmetic on them is not useful and leading zeros may be meaningful. Verify the final types instead of assuming the preview’s first few rows represent every file.

Refresh the combined files when they change

Once the query is loaded, adding a new compatible file to the source folder does not usually update the worksheet immediately. To rerun the query in desktop Excel, choose Data > Refresh All. Power Query reads the files again and applies the recorded steps. A refresh can also incorporate changes made to source files that are already in the folder.

Refresh is not a substitute for maintaining the input rules. A new workbook with a renamed column, a different sheet layout, or an incompatible data type can cause an error or produce missing or misinterpreted data. If the folder is shared, tell contributors which worksheet or table to use, what the headings should be, and whether they may add extra sheets.

For a dependable process, save the source files in a stable location and keep the output workbook outside the folder being combined. Otherwise, the output workbook itself may appear in the source list on a later refresh, particularly if it has a compatible extension. Filter temporary or irrelevant files as needed, and check the result after refreshing rather than assuming every new file was accepted correctly.

Append files or merge tables by a key?

Use the folder-combine approach when each file contains more records of the same kind. Monthly transaction logs, weekly inventory exports, and regional sales reports are typical append examples. The result has the same general columns, with records stacked vertically; a source filename column can also help identify where a row originated.

Use Power Query’s Merge Queries operation when you need to match columns from two tables using a shared key. For example, a sales table may contain ProductID and Units, while a product lookup table contains ProductID and ProductName. Merging on ProductID can add ProductName to the sales records. This is conceptually similar to a database join or a lookup, not a folder-based append.

In the Power Query Editor, the merge command is on Home > Merge Queries. Select the two queries, choose the matching column in each, and select an appropriate join kind. A key that is duplicated in the lookup table can create more rows than expected, so check that the matching field has the uniqueness you intend. If you simply need to stack similarly shaped files, use the folder combine workflow instead.

Common problems and practical fixes

  • Only some rows appear: Check the sample object, filters, and whether every file uses the same worksheet or table structure. Also inspect the final query steps for filters that may exclude data.
  • Columns are missing or misaligned: Compare headings across files. A spelling change, extra space, or different header row can make columns appear separate or cause data to land in unexpected fields. Standardize the source headings or add deliberate cleanup steps.
  • Refresh reports a file error: Look for a damaged workbook, an unrelated file, a temporary lock file, or a document that does not match the expected format. Filter the folder list by extension or filename, then refresh again.
  • Dates or numbers look wrong: Check the column type and regional interpretation. Text that looks like a date may use a different date order, such as day-month-year versus month-day-year. Confirm the intended meaning before changing the type.
  • The same records appear twice: Power Query does not automatically know that two rows represent the same business event. Check whether duplicate files or overlapping exports are in the folder. Remove duplicates only when you have a reliable definition of which columns identify a duplicate.

Errors are often easier to diagnose in the query editor than in the loaded worksheet. Select the step that first shows an error and inspect its details; a later step may only be reporting a problem introduced by an earlier transformation. Fixing the source format is usually more robust than adding complicated exceptions for every inconsistent file.

Windows, Mac, web, and alternatives

The menu path above describes Excel for Microsoft 365 on Windows. Power Query is also available in Microsoft 365 for Mac, but available connectors and authoring features can differ by Excel build. If the From Folder connector or a required option is not available in your Mac installation, create or adjust the query in desktop Excel for Windows, then check whether your Mac version supports refreshing that query.

Excel for the web does not provide an identical authoring experience to Windows desktop Excel, and support for editing or refreshing a particular query can depend on the query and account. If the query’s controls are unavailable, use the desktop application rather than assuming the workbook is broken. For a small, one-time task, manual copy-and-paste or a formula-based approach may be sufficient, but those methods are less convenient when files arrive regularly or need repeatable cleanup.

Power Query is built into supported Excel versions, so there is no separate Power Query purchase for this workflow. Access to Excel itself and some connected data sources may depend on the Microsoft 365 plan, organization settings, or permissions. If the files contain sensitive information, keep them in an approved location and make sure the workbook is shared only with people authorized to see the combined data.

Frequently Asked Questions

Can Power Query combine multiple Excel files into one worksheet?
Yes. The folder combine workflow can append compatible records and load the result to a worksheet. The number of rows must still fit within Excel’s worksheet limit if you choose a worksheet destination; larger results may need another destination or analysis tool.

Do all the workbooks need identical columns?
They should have a consistent structure for the cleanest result, but Power Query can handle some differences. Inconsistent headings or layouts may create separate columns, missing values, or errors, so standardizing files is preferable to relying on automatic interpretation.

Will the combined workbook update automatically when I add a file?
No. The query needs to be refreshed, for example with Data > Refresh All in desktop Excel. On refresh, it can read newly added files that meet the query’s filters and expected structure.

Can Power Query combine files from subfolders?
The folder connector can include files from nested folders, depending on the connector and query settings. Inspect and filter the file list by folder path if you want only the top-level folder or a particular subfolder; do not assume every discovered file belongs in the result.

What is the difference between Combine Files and Merge Queries?
Combine Files in the folder workflow usually appends rows from similar files. Merge Queries joins two tables using matching key columns. Choose append for more records of the same type and merge when you need to bring related fields together based on a key.

Related

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.