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.

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

Data > New Query > From File > From Folder

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

Step 4: You click Combine & Load.

Step 5: Click OK 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 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.

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

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.

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

Step 3: Type the command below into Terminal.

pwd

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]

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

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

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.

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.

This is the final and most complete result of merging multiple 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 online: Link
• Merge CSV files online: Link

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.

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.

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

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

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!