How to merge multiple Excel CSV files into one

Merge multiple Excel files into one and multiple CSV files into one file only take 1 minute when you have mastered following the instructions of this article. This article Lucid Gen has separate instructions for each operating system (Windows and macOS). And yet, I also add a bonus trick to help you combine the total data by horizontal and vertical titles. Let’s follow along!

Merge multiple Excel files into one

Please follow the instructions below to merge multiple Excel or CSV files into one. This method only applies to Windows computers.

Step 1: You put all Excel or CSV files into a folder like this. Remember that the folder cannot contain other types of files.

Put all Excel or CSV files that need to be merged into one folder
Put all Excel or CSV files that need to be merged into one folder

Step 2: You open the Excel software and click in this order.

Data > New Query > From File > From Folder

Merge multiple Excel files into 1 - Merge CSV files - Merge Excel files online - Import data from From Folder
Import data from From Folder

Step 3: You click the Browse. Select the folder containing the Excel or CSV files you want to merge.

Click Browse... and select the folder containing the Excel or CSV files to merge
Click Browse… and select the folder containing the Excel or CSV files to merge

Step 4: You click Combine & Load.

How to merge multiple Excel CSV files into one - Click Combine & Load
Click Combine & Load

Step 5: Click OK to merge multiple Excel files into one.

How to merge multiple Excel CSV files into one - Click OK to proceed to merge multiple Excel files into one
Click OK to proceed to merge multiple Excel files into one

This is the result after merging the Excel file, you need to turn off the Workbook table on the right and delete the extra column (column A), and you’re done.

How to merge multiple Excel CSV files into one - Results after merging Excel files
Results after merging Excel files

How to handle the error cannot be merged due to hidden files in the directory:

As I said in step 1, the folder must not contain other files to avoid errors. In case your folder has hidden files, you can handle it in 2 ways:

  1. You create a new folder, and copy files over to the new folder.
  2. You open the option to view hidden files according to the instructions below and delete the hidden file.

In the menu of the folder, you click View and select Options.

How to merge multiple Excel CSV files into one - Click View and select Options
Click View and select Options

The Folder Options window appears, click the View tab, then turn on Show hidden files, folders, and drives and then click Apply and OK.

How to merge multiple Excel CSV files into one - Turn on Show hidden files, folders, and drives
Turn on Show hidden files, folders, and drives

Merge multiple CSV files into one on Mac

Currently on Mac or macOS, there is only a way to merge multiple CSV files into one, but the XLSX file is not. To merge CSV files, follow these instructions:

Step 1: You put all the CSV files into a folder, then you click the wheel button and select Copy “folder name” as a pathname.

Merge multiple CSV files into one on Mac - Click the wheel button and select Copy "folder name" as pathname
Click the wheel button and select Copy “folder name” as a pathname

Step 2: You press Command and Space and then find and open the Terminal application available on the device.

Merge multiple CSV files into one on Mac - Open the Terminal app
Open the Terminal app

Step 3: Type the command below into Terminal.

pwd
Merge multiple CSV files into one on Mac -  Type pwd into Terminal
Type pwd into Terminal

Step 4: Type cd and paste the directory path that we copied in step 1.

cd [Đường dẫn thư mục chứa các file lúc nãy đã sao chép]
Merge multiple CSV files into one on Mac - Type cd with the directory path containing the CSV file to merge
Type cd with the directory path containing the CSV file to merge

Step 5: Enter the command below into Terminal to merge multiple CSV files into one. Note you can rename lucidgen.csv to tencuaban.csv

cat *.csv >lucidgen.csv
Merge multiple CSV files into one on Mac - Type the command merge multiple CSV files into one
Type the command merge multiple CSV files into one

Very professional like IT people, right? After a few lines of command, the CSV file is already merged.

Merge multiple CSV files into one on Mac - The merged CSV file will be in the folder
The merged CSV file will be in the folder

How to remove redundant headlines after merging:

Since this method merges the file’s entire contents, it will not remove the header line in the file. You can get rid of the superfluous headlines according to your instructions.

First, you select the columns and then Filter them (Data > Filter). Then you click the Filter button in any column. As shown below, I circled the small button with the down arrow.

Merge multiple CSV files into one on Mac - Select the columns then Filter it (Data > Filter)
Please select the columns, then Filter them (Data > Filter)

The Filter window appears. You filter out the redundant title line and then drag it to the row of numbers, right-click and delete it.

Merge multiple CSV files into one on Mac - Filter out redundant headlines and delete them xóa
Filter out redundant headlines and delete them xóa

This is the final and most complete result of merging multiple CSV files on Mac.

Merge multiple CSV files into one on Mac - Final result when merging CSV files on Mac
The final result when merging CSV files on Mac

Merge Excel and CSV files online

If you want to make it even simpler, you can use online tools to merge Excel and CSV files. However, if the file has Vietnamese data, it is very easy to error into strange characters.

  • Merge Excel files onlineLink
  • Merge CSV files onlineLink

The way to use the online Excel and CSV file merge tools is the same, they differ from the website interface, so I don’t guide with specific pictures. You need to upload the file and wait for the merge to finish, then download it.

How to combine data by column (extra bonus)

This is just a bonus, and I think you will need it. Like in this case, I run Facebook ads with multiple accounts, so I want to calculate the total number of each day.

For example: on 10/06/2020, there are 5 accounts running Facebook ads, output 5 files with date 10/06/2020, now I have to combine all data of 5 accounts on 10/06/2020 again.

Step 1: After merging the Excel or CSV file, click on an empty cell to make the cell that will receive the aggregated data table (like I chose G1), then you click Data and select Consolidate.

Click Data and select Consolidate
Click Data and select Consolidate

Step 2: You tick Top row and Left row to combine by header above and left. In the Reference box, select the entire data area, including the title.

Select 2 lable cells and drag to select the data container
Select 2 cell labels and drag to select the data container

Step 3: The date header column does not display the correct format, and you need to format the date column to Short Date.

Format the date column again
Format the date column again

That’s it, and you don’t need to sit and type commands or click plus each cell anymore.

Aggregated data by title
Aggregated data by title

Epilogue

No matter what field you work in, I believe this article will help you a lot if you have touched on reporting data. Merge multiple Excel files into one, multiple CSV files into one file, merge Excel and CSV files online, and a bonus guide to merge data by title (label). Did you solve your Excel problem? Ask questions in the comments below!

Related articles

Trần Ngọc Minh Hiếu

Trần Ngọc Minh Hiếu

I am currently working as a Data Analyst; before that, I worked in Digital Marketing. Blogging is a joy, helping me share my knowledge and experiences from life and work. You can donate to me here.

4 thoughts on “How to merge multiple Excel CSV files into one”

  1. Cám ơn b, Trần Ngọc Minh Hiếu. Vì máy của mình không có phần New Query nên mình không làm Offline được. Cám ơn b nhiều nhé!

    Reply
  2. Cách hợp nhất Online chỉ cho phép ghép tối đa 10 file thôi hả add. Có cách nào hợp nhất nhiều file hơn kg b? Chỉ giúp mình với nhé. Cám ơn b.

    Reply
    • Chào Thoa, mấy trang online người ta làm vậy để không bị mất quá nhiều dung lượng tạm thời cho mỗi người dùng đó. Muốn gộp thật nhiều file thì tốt nhất nên làm Offline. Còn online bạn phải mất công làm nhiều lần thôi à.

Leave a Comment