Analistable

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

  1. Create a folder that contains only the CSV files to combine.
  2. In a blank workbook: Data → Get Data → From File → From Folder, and choose the folder.
  3. Click Combine → Combine & Transform Data.
  4. 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).
  5. 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:

CSV problems and fixes in Power Query
SymptomCauseFix
00123 became 123ID column typed as numberSet the column type to Text (replace the current step)
Dates swapped day and monthWrong localeColumn → Change Type → Using Locale…, choose the file's locale
“é” instead of “é”File read as ANSIFile Origin 65001 (UTF-8) in the Source step
Everything in one columnWrong delimiterPick semicolon or tab in the Source step
Header rows inside the dataSome files have extra header linesFilter 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?

From Folder availability
VersionWhere 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 MacData → 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

Errors after adding new files
SymptomCauseFix
New columns from later files are missingThe sample file defines the columnsEdit 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 columnRemove the hard-coded Changed Type step and re-add types
DataFormat.Error on refreshA text value in a number columnReplace errors or change the type to Text
Duplicate rows after refreshThe same export saved twice under different namesRemove 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.

Related guides