Analistable

How to combine multiple Excel files into one spreadsheet

By the Analistable team · Updated · 3 min read

Put all the workbooks in one folder, then in Excel choose Data → Get Data → From File → From Folder, select the folder and click Combine & Transform Data. Pick the sheet to combine, click OK, then Close & Load. Excel stacks every file's rows into one table with a Source.Name column, and Refresh All picks up new files later.

Part of our guide: How to merge Excel files: every method compared

Before you start

  • Every file should have the same column headers in row 1. Small differences (“Qty” vs “Quantity”) create separate columns.
  • Close the source files. Power Query reads them from disk.
  • Keep only the files you want in the folder. Power Query includes everything it finds, including temporary ~$ files if a workbook is open.

This works in Excel for Windows (2016 and later) and in Microsoft 365 for Mac. For other ways to merge, see the overview of every method.

Step by step with Power Query

  1. Open a new, blank workbook — this will hold the combined data.
  2. Choose Data → Get Data → From File → From Folder and select the folder.
  3. Excel lists the files. Click Combine → Combine & Transform Data.
  4. In the Combine Files dialog, select the sheet or table to use from the sample file (the first file) and click OK.
  5. The Power Query Editor shows one table with a Source.Name column holding each file name. Check the column types, remove anything you don't need.
  6. Click Home → Close & Load. The combined table appears on a new sheet.

Worked example

Three regional files with the same columns become one table. The file name column tells you where each row came from:

Combined result from north.xlsx, south.xlsx and west.xlsx
Source.NameOrder IDDateProductAmount
north.xlsxN-10012026-09-02Desk420
north.xlsxN-10022026-09-05Lamp65
south.xlsxS-20012026-09-03Chair180
west.xlsxW-30012026-09-04Desk420
west.xlsxW-30022026-09-08Chair180

When the sheet names differ between files

The Combine Files dialog picks a sheet by name from the sample file. If another workbook names its sheet differently, the refresh fails with “The key didn't match any rows in the table”. To always take the first sheet regardless of name:

  1. In the Queries pane, open Transform Sample File.
  2. Select the Navigation step (the step after Source).
  3. Change the formula from something like Source{[Item="Sales",Kind="Sheet"]}[Data] to Source{0}[Data].

{0} means “the first item”, so each file's first sheet is used.

Adding next month's file

Save the new workbook into the same folder and choose Data → Refresh All. To refresh whenever the combined workbook opens, right-click the query, choose Properties and tick Refresh data when opening the file.

Excel sheets hold up to 1,048,576 rows. If the combined data is larger, load it to the Data Model (Close & Load To… → Only Create Connection, Add to Data Model) and analyse it with a pivot table.

Without Power Query

For a one-off job, drop the files into the Analistable Excel combiner. It reads every sheet, matches columns by header, adds a source column and downloads one CSV. The files never leave your computer.

Frequently asked questions

How do I combine multiple Excel files into one sheet?
Use Data → Get Data → From File → From Folder → Combine & Transform Data, choose the sheet to combine, then Close & Load. Every file's rows are stacked into one table.
Can I combine Excel files that have several sheets each?
Yes. In Power Query, choose From Folder → Transform Data instead of Combine, add a custom column with Excel.Workbook([Content]), and expand it to list every sheet of every file, then expand the Data column.
Why does Power Query fail after I add a new file?
Usually the new file's sheet has a different name, or its columns differ. Use Source{0}[Data] to take the first sheet, and rename headers to match.
Do the source files need to be open?
No. Power Query reads closed files from the folder. Close them to avoid temporary ~$ files being included.

Related guides