Merging multiple Excel files into 1 sheet and multiple CSV files into one file takes only 1 minute when you have mastered the instructions of this article. This article Lucid Gen guides individually for each operating system (Windows and MacOS). And yet, I also bonus a trick to help you pool the total figures by horizontal and vertical lable. Let's do it!
Merge multiple Excel files into 1 sheet
Follow these instructions to merge multiple Excel or CSV files into one sheet. This applies only to Windows computers.
Step 1: You put all Excel or CSV files in a folder like this. Remember, you can't contain other types of files in the folder.
Step 2: You open the Excel software and click in this order.
Data > New Query > From File > From Folder
Step 3: You click Browse... and select the folder that contains the Excel or CSV files that you want to merge.
Step 4: You click Combine & Load.
Step 5: You click OK to proceed to merge multiple Excel files into 1 sheet.
This is the result after merging the Excel file, just turn off the workbook table on the right and delete the redundant column (column A) to go.
To handle unsymed errors because there are hidden files in the folder:
As I said in step 1, the folder must not contain other types of files to avoid errors. In case your folder has hidden files, you can handle them in two ways:
- You create new folders, and copy files through new folders.
- You open the option to view the hidden file according to the instructions below and delete the hidden file.
In the folder menu you click View and select Options.
The Folder Options window appears, you click the View tab and then turn on Show hidden files, folders, and drives up, and then click Apply and OK
Merge multiple CSV files into 1 on a Macbook
Currently on Macbook or MacOS only way to merge many CSV files into 1 but XLSX files are not. To pool a CSV file you follow these instructions:
Step 1: You put all the CSV files in one folder, so that you click the wheel button and select Copy "folder name" as the path name.
Step 2: Click Command and Space, and then find the Terminal app available on your computer.
Step 3: You type the command line below into terminal.
Step 4: You type the cd and paste the folder 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: You enter the command line below into the Terminal to merge multiple CSV files into 1. Note you can rename lucidgen.csv to tencuaban.csv
cat *.csv >lucidgen.csv
Very professional as IT people right. After a few lines of command, the CSV file has been merged.
To delete redundant headlines after aggixing:
Because this merges the entire contents of the file, the subject line in the file will not be removed. You can remove redundant headlines according to your instructions.
First you select the column and then Filter it (Data > Filter). Then you click the Filter button in any column, as shown below you circle the small button with a name down there.
This is the final and most complete result of merging multiple CSV files on your Macbook.
Merge Excel and CSV files online
If you want to be even simpler you can use online tools to merge Excel and CSV files. However, if the file has Vietnamese data, it is easy to error into strange characters.
The use of Excel and CSV online file agg agg-file tools is the same, they are only different from the website interface so I do not guide with specific photos. You just need to upload the file and wait for the merge to be completed, then download it.
How to merge figures by column (bonus added)
This I just added bonus, I think there will be friends need. Just like in this case, I run Facebook ads with multiple accounts, so I want to calculate how the total figures of each day are.
For example, on 10/06/2020, there are 5 accounts running Facebook ads, 5 files are exported on 10/06/2020, now I have to merge all the data of 5 accounts in 10/06/2020.
Step 1: After merging the Excel or CSV file, you click on an empty cell to make the cell receive the agg-iteration table (as you choose G1), then you click Data and select Consolidate.
Step 2: You select Top row and Left row to merge by top and left headings. In the Reference box you select the entire area of data including the title.
Step 3: The date header column does not display the correct format, you need to format the date column again to Short Date.
That's it, you don't need to sit and type commands or click add cells anymore.
No matter what field you work in, if you have touched the report data, I believe this article will help you a lot. Merge multiple Excel files into one sheet, multiple CSV files into one file, merge Excel and CSV files online, and the bonus guides the tracking by title (lable). Have you solved the Excel problem? Please ask questions under the comment!