How to Merge Excel Files Using Power Query in Excel

How-to basics · Updated November 20, 2026

To merge Excel files with Power Query, put compatible workbooks in one folder, then in Excel for Microsoft 365 on Windows choose Data > Get Data > From File > From Folder. Select the folder, choose Combine & Transform Data, pick the worksheet or table to import, and load the resulting query. The combined table can then be refreshed when you add or update files. Power Query appends rows; if you mean matching records across two tables, use its Merge Queries command instead.

This folder method works well for recurring reports, such as monthly sales files or weekly exports, as long as the files have broadly consistent columns. It avoids copying and pasting each workbook by hand. Before you start, decide which files belong in the combined result and check that their data is arranged in a consistent, tabular layout.

What You’ll Need

  • Excel for Microsoft 365 desktop on Windows. The steps below use its Power Query folder connector. Some Power Query connectors and editing features vary on Mac and Excel for the web.
  • A folder containing the source Excel files. Keep unrelated workbooks, temporary files, and the final output workbook somewhere else.
  • Files with the same kind of records and a consistent structure. Matching column names are best, though Power Query can include columns that appear in only some files.
  • Permission to open the files and, if they are stored on a network or cloud drive, a stable path to their folder.

Step-by-Step

  1. Prepare the source files. Move the workbooks you want to combine into a dedicated folder. Each file should contain a header row and data rows, without title text, blank rows above the headers, or totals mixed into the records. Use the same header names and data types where possible. For example, if every monthly workbook has Date, Product, and Amount columns, keep those names consistent. Save and close the files before importing them.
  2. Keep the output separate from the input folder. Save the workbook that will hold the query somewhere outside the source folder. If you save it in that folder, Power Query may see the output workbook as another source file on refresh and append its data again. Also remove unrelated Excel files from the folder. If the folder must contain other files, you can filter them out in Power Query.
  3. Connect to the folder. Open the destination workbook in Excel for Microsoft 365 on Windows. On the ribbon, choose Data > Get Data > From File > From Folder. In the Folder dialog, browse to and select the folder containing the source workbooks, then choose Open or OK, depending on the dialog shown. Excel displays a preview of the files it found. Check the names and extensions before proceeding.
  4. Choose how to combine. In the preview window, choose Combine & Transform Data to inspect and shape the import before loading it. Combine & Load is a faster option when the files are already clean and you are confident about which sheet or table to use. Choose Transform Data if you first need to filter out files or inspect the file list. For a first-time import, Combine & Transform Data is usually the safer choice.
  5. Select the object to import. Power Query opens a Combine Files dialog based on a sample workbook. Choose the worksheet or Excel table that contains the records, then inspect the preview. If each workbook has a named table with the same name, selecting that table is often more dependable than selecting a worksheet with changing layouts. If the dialog offers a sample file choice, use a representative workbook that has the expected structure. Click OK to continue.
  6. Review the combined query. Power Query Editor shows the appended records and a set of applied steps. Check that the first row is being treated as headers, the expected columns are present, and the data is coming from the right sheet or table. The generated helper queries support the combine process; you can usually leave them intact. If you see file names or navigation details instead of the records, revisit the selected object in the Combine Files step.
  7. Filter or clean the data if needed. Use the filter buttons on column headers to remove unwanted rows or values. For example, filter the file list before combining to exclude temporary files, or remove blank records from a required field. To change a column’s type, select it and choose the appropriate option from Transform > Data Type, such as Date, Text, or Decimal Number. Avoid changing identifiers such as product codes to numbers if leading zeros matter.
  8. Load the result into Excel. When the preview is correct, choose Home > Close & Load to load the query result as a table on a worksheet. If you want to choose where it goes, use Home > Close & Load > Close & Load To, then select a table and a new or existing worksheet. Loading to the Data Model is another option for larger analyses, but a worksheet table is usually easiest to inspect.
  9. Check the output against the sources. Confirm that the column headings and sample rows look right. Compare the combined row count with the number of data rows in the source workbooks, allowing for any header rows or rows you intentionally filtered out. Check a few records from different files, not just the first file, since later workbooks may have different formats or missing fields.
  10. Refresh when the files change. Add new workbooks to the same folder or replace existing files with updated versions that use the same structure. In the destination workbook, choose Data > Refresh All to rerun the query and update the table. If you move or rename the source folder, edit the folder path in the query’s Source step or reconnect to the new location. Save the destination workbook after a successful refresh.

Common Mistakes

Confusing append with a database-style merge. Combining files from a folder stacks records into one result, like putting one monthly list beneath another. It does not match rows from separate files on a shared key. To bring columns from one table into another by a common field such as Customer ID, first create queries for both sources, then in Power Query Editor choose Home > Merge Queries or Home > Merge Queries as New. Select the matching columns and join type, then expand the resulting column to choose which fields to add.

Different layouts across workbooks. Power Query uses a sample file to create steps for processing all files. If some workbooks put the data on a different sheet, use different table names, or place notes above the headers, the sample-file steps may not work for every file. Standardize the source layout where practical. If the files cannot be standardized, they may need separate queries and more deliberate transformation steps.

Headers or totals treated as data. A title above the header row can make Power Query import the wrong row as column names. Totals at the bottom can also be appended as if they were ordinary records. Remove these rows at the source or use Power Query’s row-removal and header-promotion tools, then verify the result in more than one file.

Accidentally including extra files. Temporary files, old versions, or the destination workbook can add duplicate or irrelevant rows. Keep the source folder dedicated to inputs, and use the file-list preview or filters to limit the query to the intended extensions and names.

Tips

  • Use Excel tables in each source workbook when you can. Give the tables a consistent name and keep their column headers stable. Tables are less likely than loosely arranged worksheet ranges to include stray notes or blank columns.
  • Keep a small, representative sample workbook in the folder while setting up the query. Test with files that include typical edge cases, such as a blank optional field or a longer list, before relying on the refresh.
  • Preserve a source filename column if you need to trace an output row back to its workbook. The folder connector includes file metadata in its file list; in Power Query, retain the relevant Name field and make sure it is carried into the combined result if traceability matters.
  • Decide how to handle inconsistent columns. Power Query can align fields by column name, so a column missing from one file can have blank values for those rows. A spelling difference, such as Customer ID versus CustomerID, creates separate columns rather than recognizing them as the same field.
  • For recurring work, keep the folder path stable and document where colleagues should add new files. Refresh All updates the query, but it does not fix changed source layouts automatically.
  • On Mac, Power Query availability and connector options depend on the Excel build. Look under Data for Get Data (Power Query) and check whether the folder connector is offered. If it is not available, use desktop Excel for Windows for this workflow or import the files through a connector supported by your Mac version. Excel for the web can handle some query refresh and editing scenarios, but the full folder-combine setup is best done in desktop Excel.

Troubleshooting

The preview does not list all files. Check that the files are in the selected folder, that their extensions are supported, and that you have access to the location. If the folder contains subfolders, remember that the folder connector may list files differently depending on the connector and settings; filter or choose the appropriate folder source rather than assuming every nested file is included.

Only one workbook’s data appears. Reopen the query in Data > Queries & Connections, then edit it and inspect the Combine Files settings and sample object. Make sure the query is combining the intended worksheet or table and that the selected object exists in the other workbooks. A worksheet name mismatch or inconsistent table naming can prevent the expected rows from appearing.

Some values appear under the wrong headings or as null. Check whether the source files use identical header text and whether the selected row is actually the header row in every file. Power Query matches fields by name, not by their visual position alone. Rename inconsistent columns during transformation, and inspect files with null values to see whether the field is genuinely empty or named differently.

Dates or numbers look wrong. A column may have been assigned the wrong data type, or source workbooks may use different regional date formats. In Power Query Editor, select the column and use Transform > Data Type. For ambiguous text dates, choose the option to change type using a locale when available, and select the locale that matches the source data. Check a few known values before loading.

Refresh fails after moving the files. The query refers to the original folder path. Edit the query’s Source step in Power Query Editor and browse to the new location, then refresh. For a shared workbook, make sure other users can access the same path; a folder mapped to a drive letter on one computer may not use the same path on another.

The output contains duplicates. Confirm that the source folder does not contain old copies or the destination workbook itself. Power Query appends every included file; it does not automatically know that two rows represent the same record. Filter out unwanted files or remove duplicates using a suitable key only if duplicate records should truly be discarded.

Frequently Asked Questions

Does Power Query combine files by adding rows or matching columns?
Combine Files from a folder appends rows from the selected object in each workbook. To match records across two datasets and add fields, use Merge Queries and select a common key.

Do all Excel files need exactly the same columns?
No, but consistent column names and types make the result easier to use. Power Query can include columns that appear in only some files; rows from files without that field will generally show blank values there. Different spellings can create separate columns.

Can I combine files with different worksheet names?
It depends on how the files are organized and what object you select in the Combine Files dialog. A consistent named table in each workbook is often easier to combine. If worksheet names or layouts vary, adjust the query or standardize the files before combining.

Will the combined table update automatically?
It updates when you refresh the query, for example with Data > Refresh All. You can also configure refresh behavior in the query or workbook connection properties, but new files are not guaranteed to appear in the output until the query refreshes.

Can I combine CSV files with the same method?
Yes. Use Data > Get Data > From File > From Folder, select the folder, and choose the combine option. Check delimiter, encoding, and data types in the preview, especially if the CSV files use different regional formats.

Can Power Query combine files stored in OneDrive or SharePoint?
It can, but the best connector and path depend on how the files are stored and your organization’s access settings. A locally synchronized OneDrive folder may work with the folder connector; for SharePoint libraries, use a suitable SharePoint connector where available and filter to the intended files.

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.