How to Combine Multiple Excel Files with Power Query

How-to basics · Updated November 19, 2026

The easiest way to combine multiple Excel files in Microsoft 365 on Windows is Power Query: put the files in one folder, then choose Data > Get Data > From File > From Folder and select Combine & Transform Data. Power Query can append the rows into one table and refresh the combined result later when you add or update files.

What combining Excel files means

“Combine” can mean a few different things. Most commonly, people have several workbooks with the same columns—for example, monthly sales files—and want one longer list. That is an append operation: the rows from each file are placed beneath the rows from the others. Power Query’s folder method is designed for this task.

Other requests need a different approach. If each workbook contains a different kind of information, or you want values from separate sheets positioned side by side, simply appending files may not produce the result you intend. You may need to merge tables using a shared field such as an order number, or summarize figures from similarly arranged ranges. Decide whether you want one longer table, a joined table, or a summary before choosing a method.

Why Power Query is usually the easiest option

Power Query is Excel’s built-in tool for importing and shaping data. Instead of repeatedly opening files, copying cells, and pasting them into a master workbook, you create a set of steps once. The query can then read files from a folder, combine their data, and apply the same cleanup actions to each refresh.

This is particularly useful for recurring work. Imagine that four regional teams send workbooks with columns for Date, Region, Product, and Units. If the files follow the same basic layout, Power Query can append their records into a single table. When next month’s files arrive, you can place them in the folder and refresh the query rather than rebuild the combined list by hand.

  • Use a folder query when many files have the same kind of data and you expect more files later.
  • Copy and paste can be quicker for a one-time task involving just a few small files.
  • Merge queries when you need to match records across tables using a common identifier, rather than stack their rows.

Prepare the files before combining them

Power Query is flexible, but consistent source files make the result much more reliable. Put the intended files in a dedicated folder. Avoid mixing in unrelated workbooks, old output files, or notes. If you save the combined workbook in the same folder that the query reads, the output workbook may be picked up as a source on a later refresh. A separate folder for the final workbook helps prevent that problem.

Check that the files have a compatible structure. Ideally, each one has the same column names, with headers in the same row and records beneath them. A column called “Customer ID” in one file and “Client” in another may be treated as two separate columns. Likewise, a title row above the headers or totals at the bottom can be interpreted as data unless you remove or filter those rows in the query.

  • Use the same worksheet or Excel table name in every workbook when possible.
  • Keep data types consistent—for example, store dates as dates and quantities as numbers.
  • Remove password protection or access restrictions that prevent Excel from reading the source files.
  • Keep a backup of the original files; the query creates a combined result but does not replace the sources.

Combine files from a folder in Excel

  1. Open a blank or destination workbook. In Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From Folder.
  2. Choose the source folder. Browse to the folder containing the workbooks, select it, and choose Open. Excel displays a list of the files it finds. Check that the list contains the intended sources.
  3. Start the combine operation. Choose Combine, then Combine & Transform Data. The Combine option may appear as a button or a menu depending on the Excel build. Transform Data opens the Power Query Editor so you can inspect and adjust the result before loading it.
  4. Select the data object. In the Combine Files dialog, choose the worksheet or table containing the records. If the workbooks use Excel tables, selecting the table is often more dependable than selecting a sheet with extra notes or formatting around the data. Confirm the preview and choose OK.
  5. Review the combined query. Excel creates a query that uses one file as a sample and applies the combine steps to the others. In the Power Query Editor, check the column names, data types, and sample rows. If the first row contains headers, promote it to headers if Excel has not already done so.
  6. Load the result. Choose Home > Close & Load to place the result in a worksheet as an Excel table. If you need to choose a destination or create a connection without loading a table immediately, use Home > Close & Load To.

The result is a new table; the source workbooks remain separate. Power Query may also include a column identifying the source file, which can be useful for tracing a record back to where it came from. Keep that column if you need an audit trail, or remove it in the editor if it is not useful.

Refresh the combined data when files change

After the initial setup, save new source workbooks in the folder the query uses. To update the output, select Data > Refresh All, or right-click the output table and choose Refresh. Excel reruns the query steps against the available source files. If you edited a source file, the changes should appear after a successful refresh.

Refresh does not necessarily mean “look everywhere for new files.” The query reads the folder path and any filters set when it was created. Moving the folder, renaming it, or changing a file extension can break the source connection. If the folder location changes, edit the query’s source step in Power Query Editor. If you only want certain files included, filter the file list—for example, by extension or filename—before combining.

It is also wise to check the output after a refresh, particularly when the source layout changes. New columns, renamed headers, or a different worksheet name may require changes to the query. Refresh automates repeatable steps; it does not guarantee that every future file will match the original assumptions.

Common problems and practical fixes

  • Columns appear separately instead of lining up. Check spelling and spacing in the headers. Power Query uses column names to align fields, so inconsistent names can create separate columns with blanks in some rows.
  • Some files are missing. Confirm that they are in the selected folder and are not excluded by a filter. Check extensions too: a query set up for Excel workbooks may not include CSV files unless you adjust its steps.
  • The wrong worksheet or table was combined. Reopen the query and inspect the sample-file and navigation steps. Select the correct data object, and make sure that object is present in each source workbook.
  • Headers or totals show up as records. Remove extra title rows, promote the correct header row, and filter out totals or blank lines in the Power Query Editor. Apply these transformations to the combined query so they are repeated on refresh.
  • Dates or numbers look wrong. A value may have been interpreted as text or using a different regional date convention. In the editor, set the correct data type and check that dates are not being mistaken for month/day values when the source uses day/month order.
  • The query reports an access or file error. Close source workbooks that are being edited, confirm you have permission to the folder, and check whether a file is corrupt or protected. A temporary lock file or unrelated document in the folder can also interfere; filter the file list to include only the intended sources.

When another method is a better fit

For a one-time combination of two or three very small files with identical columns, manual copying may take less setup. Copy only the data rows, paste them beneath the existing records, and check that you have not pasted duplicate headers in the middle of the list. This is simple, but it is easy to miss a file or repeat rows when the job recurs.

If you want a summary from matching layouts rather than a detailed list, Excel’s Data > Consolidate feature may suit the task. It can consolidate values from multiple ranges using operations such as Sum or Average, with labels used to match corresponding rows or columns when appropriate. It is not the same as creating one complete, refreshable record-level table, so it is not a replacement for Power Query in every situation.

Power Query also offers merge operations for joining information by a key. For example, you might have a transaction table and a separate customer table; merging on Customer ID can bring customer details onto transaction rows. Appending those two tables would instead stack unrelated records, which is usually not what you want. For repeated or larger jobs, avoid relying on a macro unless you have a clear reason and someone able to maintain it.

Excel for Mac, Excel for the web, and access

The steps above are written for Excel for Microsoft 365 on Windows, where the folder connector is available through Get Data. Power Query capabilities and connectors can differ by Excel for Mac version and update channel. On a Mac, check the Data tab for the Power Query/Get Data options and confirm that the folder connector is offered in your installation; if it is not, you may need to use a Windows desktop version for this workflow or choose a manual alternative.

Excel for the web does not provide the same full set of Power Query authoring options as the Windows desktop app. If you need to create or substantially edit a folder-combine query, open the workbook in desktop Excel. Access to Power Query does not normally require a separate add-in purchase in Microsoft 365 desktop Excel, but available features depend on the Excel edition and platform. Keep sensitive source files in locations your organization permits, and be careful about sharing a workbook that contains imported data.

Frequently Asked Questions

Can Excel combine multiple workbooks into one sheet automatically?
Yes. In desktop Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Folder and choose Combine & Transform Data. After setup, add matching files to the source folder and refresh the query.

Do all files need to have the same columns?
They should have compatible data and consistently named columns for the cleanest result. Power Query can handle some differences, but inconsistent headers may create extra columns, and missing fields will be blank for files that do not contain them.

Will combining files delete or change the original workbooks?
No. The query reads the source files and loads a separate result into the destination workbook. It does not merge the originals into one physical workbook or overwrite them.

Can I combine files that have different sheet names?
Sometimes, but the folder-combine workflow is simplest when every file contains the same table or worksheet structure. If names differ, inspect the sample-file navigation steps and adjust the query; otherwise, standardize the sheet or table names before combining.

Can I add new files after I have created the query?
Yes, provided the files are placed in the folder the query reads and match its expected structure. Use Data > Refresh All to update the output. If a new file has different headers, a different data object, or an incompatible format, correct the source or edit the query before relying on the refreshed result.

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.