How to merge CSV files in Excel

This works for any CSVs with the same columns. For bank statements specifically, there is a shortcut: add the PDF statements to “Several statements” in the converter and download one combined CSV that is already checked for gaps, duplicates and overlaps. See combining bank statements.

Step by step

1. Put the CSVs in one folderOnly the files you want to combine, all with the same column headings.
2. Get data from the folderIn Excel, go to Data, Get Data, From File, From Folder, and choose the folder.
3. CombineClick Combine, then Combine & Transform Data. Excel uses the first file as a sample of the layout.
4. Check the columnsIn Power Query, check dates are dates (use Change Type, Using Locale, English (United Kingdom) if not) and amounts are numbers. The Source.Name column shows which file each row came from.
5. LoadClick Close & Load. All rows appear in one table.
6. Refresh next monthDrop the new CSV in the folder and click Data, Refresh All.

The duplicates problem

Power Query combines everything in the folder. If the same statement was saved twice, or an interim statement overlaps two monthly ones, those transactions are combined twice. Removing duplicate rows doesn’t fix it safely: two genuine identical transactions on the same day would be removed too. Check the set first, or use the converter’s combined CSV, which removes repeated statements and overlaps while keeping genuine repeats.

Keep the file name

Don’t delete the Source.Name column. When a figure is questioned later, it tells you which CSV, and so which statement, a row came from. Rename it to “Source file” if you like.

If the folder also contains files you don’t want, such as a notes file or an older export, filter the Extension or Name column in Power Query to leave them out before combining.

Sort after combining

Power Query combines files in the order it finds them, usually alphabetical by file name. Sort the combined table by date before you rely on any running balance.