How to combine multiple Excel files into one spreadsheet
By the Analistable team · Updated · 5 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
- Open a new, blank workbook — this will hold the combined data.
- Choose Data → Get Data → From File → From Folder and select the folder.
- Excel lists the files. Click Combine → Combine & Transform Data.
- In the Combine Files dialog, select the sheet or table to use from the sample file (the first file) and click OK.
- 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.
- 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:
| Source.Name | Order ID | Date | Product | Amount |
|---|---|---|---|---|
| north.xlsx | N-1001 | 2026-09-02 | Desk | 420 |
| north.xlsx | N-1002 | 2026-09-05 | Lamp | 65 |
| south.xlsx | S-2001 | 2026-09-03 | Chair | 180 |
| west.xlsx | W-3001 | 2026-09-04 | Desk | 420 |
| west.xlsx | W-3002 | 2026-09-08 | Chair | 180 |
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:
- In the Queries pane, open Transform Sample File.
- Select the Navigation step (the step after Source).
- Change the formula from something like
Source{[Item="Sales",Kind="Sheet"]}[Data]toSource{0}[Data].
{0} means “the first item”, so each file's first sheet is used.
Combining every sheet of every file
From Folder → Combine takes one sheet per file. When each workbook holds several sheets (one per week, say), build the query yourself. Choose Data → Get Data → From Other Sources → Blank Query, open the Advanced Editor and paste this, changing the folder path:
let
Source = Folder.Files("C:\Reports\Regions"),
Workbooks = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
WithSheets = Table.AddColumn(Workbooks, "Sheets", each Excel.Workbook([Content], true)),
Expanded = Table.ExpandTableColumn(WithSheets, "Sheets", {"Name", "Data", "Kind"}, {"Sheet", "Data", "Kind"}),
OnlySheets = Table.SelectRows(Expanded, each [Kind] = "Sheet"),
Tagged = Table.AddColumn(OnlySheets, "Tagged", each Table.AddColumn([Data], "Source", (row) => [Name] & " / " & [Sheet])),
Combined = Table.Combine(Tagged[Tagged])
in
CombinedThe true in Excel.Workbook promotes each sheet's first row to headers. Table.Combine matches columns by name, so sheets with an extra column keep it rather than losing it, and the Source column records the file and sheet of every row. The filter on ~$ skips the lock files Excel creates while a workbook is open.
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.
Checking the combined table
Before using the result, confirm every file arrived in full. In the Power Query Editor, select Source.Name and choose Home → Group By, with a Count Rows operation. Compare each count with the number of data rows in the original file (its last row number minus one for the header). In the worked example above, north.xlsx should show 2, south.xlsx 1 and west.xlsx 2, for 5 rows in total.
Also scroll the Amount column for errors or blanks. A single file that stores amounts as text produces errors after the type change step, and those rows disappear if you later use Remove Errors without looking.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Header text appears as a data row | One file has a title row above the headers | Remove the title row in that file, or add Remove Top Rows in Transform Sample File |
| Columns called Column1, Column2… | Headers weren't promoted | Home → Use First Row as Headers in Transform Sample File |
| A column is missing for some files | Header spelt differently in those files | Rename to match, or see the different-columns guide |
| Error rows in the Amount column | Text such as “n/a” in a number column | Replace Errors, or clean the source |
| Rows from an old file still appear | The file is still in the folder | Move old files to an archive subfolder outside the source path |
Note that From Folder also reads subfolders. If you keep an archive folder inside the source folder, filter the Folder Path column to exclude it.
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.