How to combine CSV files in Excel
By the Analistable team · Updated · 5 min read
Put the CSV files in one folder, then in Excel choose Data → Get Data → From File → From Folder, select the folder and click Combine & Transform Data. In the Combine Files dialog, check the File Origin (use 65001: Unicode UTF-8) and Delimiter, click OK, then Close & Load. All files end up in one table with a Source.Name column, and Refresh All adds new files later.
Part of our guide: How to merge CSV files into one
Step by step
- Create a folder that contains only the CSV files to combine.
- In a blank workbook: Data → Get Data → From File → From Folder, and choose the folder.
- Click Combine → Combine & Transform Data.
- In the dialog, set File Origin to 65001: Unicode (UTF-8) if your files contain accented characters, and choose the right Delimiter (comma, semicolon or tab).
- Click OK. In the Power Query Editor, check each column's type (see the next section), then Close & Load.
The result is one Excel table. To add next week's file, save it in the folder and choose Data → Refresh All.
Fix the column types before loading
Power Query adds a Changed Type step that guesses types from the first rows. That guess causes the classic CSV problems:
| Symptom | Cause | Fix |
|---|---|---|
| 00123 became 123 | ID column typed as number | Set the column type to Text (replace the current step) |
| Dates swapped day and month | Wrong locale | Column → Change Type → Using Locale…, choose the file's locale |
| “é” instead of “é” | File read as ANSI | File Origin 65001 (UTF-8) in the Source step |
| Everything in one column | Wrong delimiter | Pick semicolon or tab in the Source step |
| Header rows inside the data | Some files have extra header lines | Filter out rows equal to the header text |
One sheet per CSV instead of one table
If you want a workbook with each CSV on its own tab rather than one combined table, open each file and use Move or Copy to move its sheet into one workbook, or run a short macro. Most analysis is easier on one combined table, though, because pivots and formulas can use all rows at once.
Combining a single extra file
To add one CSV to an existing table without a folder query, use Data → From Text/CSV, load it as a connection, and Append it to the existing query (Data → Get Data → Combine Queries → Append).
Excel sheets hold 1,048,576 rows. If the combined CSVs are bigger, load the query to the Data Model only, or combine them outside Excel — see merging CSV files.
No Excel needed
The free CSV combiner matches headers by name, keeps one header row and adds a source column, then downloads a single CSV you can open in Excel. It runs in your browser, so the files aren't uploaded.
Which Excel versions can do this?
| Version | Where to find it |
|---|---|
| Microsoft 365 and Excel 2019/2021/2024 (Windows) | Data → Get Data → From File → From Folder |
| Excel 2016 (Windows) | Data → New Query → From File → From Folder |
| Excel 2010 and 2013 (Windows) | Free Power Query add-in, Power Query tab → From File → From Folder |
| Microsoft 365 for Mac | Data → Get Data (Power Query) → From Folder |
Menu names vary slightly between builds, but the Combine Files dialog and the steps after it are the same. Excel for the web can refresh some queries but is not the place to build a folder query.
Keep only the files you want
From Folder reads every file in the folder, including subfolders, Excel files and any stray notes. Before combining, click Transform Data instead of Combine, filter the Extension column to .csv and, if needed, filter Name (for example, names that begin with “sales_”). Then click the combine button in the Content column header. This also stops the combined output from being picked up if you save it in the same folder.
Worked example: three monthly exports
A shop exports orders as orders_2026-07.csv, orders_2026-08.csv and orders_2026-09.csv, with 412, 389 and 455 data rows. After combining:
- The table should have 412 + 389 + 455 = 1,256 rows. Check the row count shown at the bottom of the Power Query preview, or with
=ROWS(Table1)after loading. - Source.Name holds the file name for each row. Use Split Column → By Delimiter on “_” and “.” to turn it into a Month column for pivot tables.
- Next month, drop orders_2026-10.csv into the folder and Refresh All. The row count should rise by exactly that file's row count.
Troubleshooting the combine step
| Symptom | Cause | Fix |
|---|---|---|
| New columns from later files are missing | The sample file defines the columns | Edit the Transform Sample File query, or remove the step that selects named columns |
| “The column … of the table wasn't found” | A file renamed or removed a column | Remove the hard-coded Changed Type step and re-add types |
| DataFormat.Error on refresh | A text value in a number column | Replace errors or change the type to Text |
| Duplicate rows after refresh | The same export saved twice under different names | Remove the copy, or remove duplicates on the order ID |
Frequently asked questions
- How do I merge multiple CSV files into one Excel file?
- Use Data → Get Data → From File → From Folder → Combine & Transform Data, then Close & Load. Every CSV in the folder is stacked into one table.
- Why does Excel remove leading zeros from my CSV?
- Excel or Power Query converts the column to a number. In Power Query, change the column type to Text before loading.
- Can I combine CSV files with different columns in Excel?
- Yes. Power Query matches columns by header name. Make sure the combine step uses all columns, not just the first file's — see combining files with different columns.
- Does Power Query change my original CSV files?
- No. It only reads them. All cleaning steps apply to the combined table in Excel, and the files in the folder stay as they were.
- How do I stop the combined file being read as an input?
- Save the workbook outside the source folder, or filter the folder query to the .csv extension and a file-name pattern so only the exports are combined.