Combining multiple Excel files into 1 sheet and multiple CSV files into one file only take 1 minute once you have mastered the instructions in this article. This Lucid Gen article provides separate instructions for each operating system (Windows and macOS). And yet, I also add a bonus to help you combine the total figures by title (label) horizontally and vertically. Let’s follow along!
Merge multiple Excel files into one sheet
Please follow the instructions below to merge multiple Excel or CSV files into one sheet. This method only applies to Windows computers.
Step 1: You put all Excel or CSV files into a folder like this. Remember that the directory cannot contain any other file types.
Step 2: Open the Excel software and click in this order.
Data > New Query > From File > From Folder
Step 3: You click the Browse .. . and select the folder containing the Excel or CSV files you want to merge.
Step 4: Click Combine & Load.
Step 5: Click OK to merge multiple Excel files into one sheet.
This is the result after merging the Excel file, you just need to turn off the Workbook table on the right and delete the excess column (column A).
How to handle errors that cannot be merged due to hidden files in the directory:
As I said in step 1, the directory must not contain other types of files to avoid errors. In case your folder has hidden files, you can handle it in two ways:
- You create a new folder, and copy the files to the new folder.
- You open the option to view hidden files according to the instructions below and delete hidden files.
In the folder’s menu, click View and select Options.
Folder Options window appears, click the View tab , then turn on Show hidden files, folders, and drives, then click Apply and OK.
Combine multiple CSV files into 1 on Macbook
Currently, on a Macbook or MacOS, there is only a way to merge multiple CSV files into one, but XLSX files are not. To merge CSV files, follow the instructions below:
Step 1: You put all CSV files into a folder, then click the wheel button and choose Copy “folder name” as path name.
Step 2: Press Command and Space, then find and open Terminal application available on your computer.
Step 3: Type the command below 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]
Step 5: Enter the following command into Terminal to merge multiple CSV files into 1. Note you can rename lucidgen.csv to tencuaban.csv
cat *.csv >lucidgen.csv
Very professional like IT people right. After a few lines of code, the CSV file is merged.
How to remove redundant headlines after merging:
Because this method includes the entire contents of the file, it will not remove the subject line in the file. You can remove redundant headlines according to your instructions.
First, you select the columns and then Filter it (Data> Filter). Then you click the Filter button in any column, as shown below, I circled the small button with the arrow down.
Filter window appears, you filter out redundant headlines and then drag a series of numbers, right-click and delete.
This is the final and most complete result of merging multiple CSV files on a Macbook.
Merge Excel and CSV files online
If you want even more simplicity 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.
The way to use the tools to merge Excel and CSV files online is the same, they just differ from the website interface, so I do not guide with specific pictures. You just need to upload the file and wait for it to be merged, then download it.
How to combine data by column (additional bonus)
This is just a bonus, I think there will be you. Like in this case, I run Facebook ads with multiple accounts, so I want to calculate how the daily total is.
For example: on 10/06/2020 there are 5 accounts running Facebook ads, then output 5 files with the date June 10, 2020, now I have to include all the data of 5 accounts on June 10, 2020. again.
Step 1: After merging the Excel or CSV file, you click on an empty cell to make the cell receive the combined data table (as you choose G1), then click Data and select Consolidate.
Step 2: You tick Top row and Left row to merge by the title above and on the left. In the Reference box, select the entire data area , including the title.
Step 3: The date column does not display the correct format, you need to format the date column as Short Date.
That’s it, you do not need to sit and type the command or click plus each cell.
No matter what industry you work in, if you have touched on the reported data, I believe this article will help you a lot. Merge multiple Excel files into 1 sheet, multiple CSV files into one file, merge Excel and CSV files online, and a bonus section to guide data aggregation by title (label). Have you solved the Excel problem? Please ask questions below the comments!