Here, we have the following three files January.xlsx, February.xlsx, and March.xlsx containing the sales records for the January, February, and March months of a company.
We have used the Microsoft Excel 365 version here, but you can use any other version.
Step 1 – Saving Excel Files as CSV Format in a Folder
- Go to the File Tab.
- Select the Save As option and then click on the dropdown symbol beside the Save Option.
- Choose the option CSV UTF-8 (Comma delimited) from the list of other formats.
- Change the file name from January to 1-January (the serial numbers preceding the file name will arrange the files serially).
- Click on the Save option.
We now have our new file name with the new format as 1-January.csv.
Change the file formats for the other two files:
- For the February.xlsx file select the option CSV UTF-8 (Comma delimited) from the list of other formats, change the name to 2-February, and click on Save.
After that, you will get the new filename 2-February.csv instead of February.xlsx.
- For the March.xlsx file, select the option CSV UTF-8 (Comma delimited) from the list of other formats, change the name to 3-March, and click on Save.
You will get the new filename 3-March.csv.
Put all three files in a folder named Multiple Files so they are arranged serially. We will need the system path to the folder later.
Step 2 – Opening CMD or Command Prompt to Merge Excel Files into One
Press the WINDOWS key + R to open the Run wizard.
- Type cmd in the Open box and press Enter or click on OK to run the command prompt.
This opens the Command Prompt.
Step 3 – Merging Excel Files into One Using CMD
We need to copy the path of the folder Multiple Files in which we have stored our Excel files in CSV format to be merged:
- Go to the folder.
- Expand the ribbon by clicking on the drop-down symbol in the top-right corner or pressing Ctrl + F1.
- Select the folder Multiple Files and then click on the Copy path option.
- Now go to the Command Prompt, where we need to change the active directory to go to the path of our folder Multiple Files:
- Type cd and a space.
- Press Ctrl + V.
- Press Enter.
- Type copy *.csv combined.csv (where combined is our new filename).
- Press Enter and wait for the confirmation text indicating that a file was copied.
- Close the Command Prompt.
- After closing the CMD window, go to the folder Multiple Files where you will have the new merged file named combined.csv.
Step 4 – Opening New Created File and Save in XLSX Format
After opening the combined.csv file, we can see the Sales Record of January header first and then the corresponding values of the January file. In this way, the records of the February file and March file are also appearing serially.
- To see the remaining values scroll down throughout the sheet.
Here’s how the rest of the file should look, with some remnants from copying inserted at the end.
Now we’ll save the file in the .xlsx format:
- Go to the File tab.
- Click on the Save As option.
- Select the Excel Workbook option and then the Save option.
Finally, the name of the merged file is now combined.xlsx.
So, after following the aforementioned steps we have merged 3 Excel files into one with the help of CMD.
Download Workbook
<< Go Back To Merge Excel File | Merge in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi! wanted to know what is the limit of entries which can be merged when using this method? I got to know that the limit of this merger method is upto 30k entries only. Please correct me if I am wrong or else what can be the limit of this?
Regards,
Muizz
Hello Muizz Shaikh,
Thanks for your question. The maximum number of entries should be within the limit of the maximum row numbers of Excel which is 1,048,576. So, you can merge the files as long as the entries of the combined file support this limit.