Al Ikram Amit
If Excel users are seeking strategies to combine many Excel files into one, you’ve come to the correct spot. You may need to merge excel sheets into one workbook at times and to do it fast, you may use a few approaches. Let’s begin with this article.
Download Practice Workbook
You can download the practice workbook from the following download button.
4 Ways to Merge Excel Sheets into One Workbook
Here, we have three files: Sales Record of June.xlsx, Sales Record of July.xlsx, and Merged Worksheet.xlsx, which include the sales records for the months of June, July, and Merged Worksheet for a firm. We have outlined four procedures to merge these files into a single Excel file in the following sections.
1. Merge Excel Sheets into One Workbook Using Consolidate Feature
The data range must be at the same location on each source sheet when we open the sheet.
📌 Steps:
- Click the upper-left cell of the region in the destination sheet where we wish the aggregated data to display.
- We must navigate to the Data tab and then choose Consolidate in the Data Tools group.
- In the Consolidate box, select the function that Excel should use to consolidate the data.
- Choose the data in each source sheet and under Use labels in marked the Left column.
- After selecting one data range from one sourcebook, press Enter. Notice in the image, all references have the file path.
- Repeat the procedure as deemed appropriate because our example contains two source worksheets that will be integrated in a third workbook.
- Finally, here is the summed-up version of the two data ranges from different worksheets.
Read More: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)
2. Merge Excel Sheets into One Workbook Using Paste Link Option
Options in Excel allow users to refer to cells in other Excel files or workbooks. This enables the combination of Excel files by referencing them in the master file.
📌 Steps:
- We must open both Excel files. Then, copy the first cell from the source worksheet to which we wish to refer.
- We’ll now go at the master file. Right-click on the cell where we want the data to be shown. Select Paste Link from the Paste Special menu.
- This will provide the correct formula. It will look like this in the image.
- To see the additional cells, alter the formula from absolute to relative cell referencing by deleting the dollar signs ($), as seen below. Drag horizontally and then drag downward to fill the table.
- Here is the final range from another worksheet.
Read More: How to Merge Excel Worksheets Without Copying and Pasting
3. Combine Excel Sheets into a New Workbook Using Move or Copy Sheet Command
This command is available in the Format drop-down of the Cells group. Apply the following steps to utilize this.
📌 Steps:
- Click the Home tab from the top ribbon. Tap Format in the Cells group. Choose either Move or Copy Sheet.
- This brings up the Move or Copy dialog box. The To book option allows choosing the main spreadsheet to which all of the individual sheets should be sent. Use the Before sheet box to determine the order of the sheets (if you’re using a fresh book, this will be blank). When you’re finished, click OK.
- As a result, below is the final range from a different spreadsheet.
Read More: How to Merge All Sheets into One in Excel (6 Quick Ways)
4. Merge Excel Sheets into One Workbook Using Command Prompt
In this procedure, we will launch the CMD or Command Prompt and use it to merge the numerous Excel files into one.
📌 Steps:
- To merge numerous Excel files into one, first convert the XLSX files to CSV files. Navigate to the File Tab.
- Select the Save As option and then click the menu icon next to the Save Option. Then, in the list, you will find many file formats; explore them and select CSV UTF-8 (Comma delimited) from the list of other formats. Additionally, rename the file from Sales of July to 1-Sales of July (the serial numbers preceding the file name will arrange the files serially according to which serial we want to merge them). Finally, select the Save option.
- Then we’ll have a new file with the new format, 1-Sales of July.csv.
- To begin, Copy path to the folder Multiple Files where we have saved our Excel files in CSV format to be combined.
- When you press the WINDOWS key + R, the Run window will appear. To launch the command prompt, type cmd in the Open box and hit OK.
- We’ve opened the CMD, or Command Prompt, as you can see.
- Enter cd and a space after it. To paste the copied path of our Multiple Files folder, press CTRL+V or right-click on your mouse.
- After clicking ENTER, you will be sent to the directory specified in the preceding line.
- Now, in the new line, put copy *.csv Merged.csv (where Merged Worksheet is our new filename), followed by >.
- After clicking ENTER, the following command lines will be created automatically, where we can see the two file names that we wish to combine.
- After you close the CMD window, navigate to the Multiple Files folder, where you will find the new merged file titled Merged.csv.
- When we view the Merged.csv file, we notice the Sales Record of July heading first, followed by the equivalent numbers from the June file.
- Navigate to the File tab to save the file in XLSX format as shown in the below image.
Read More: How to Merge Multiple Sheets in Excel (3 Easy Ways)
Conclusion
In this post, we attempted to explain the methods for combining many Excel sheets into a single workbook. I hope you find it beneficial. If you have any ideas or questions, please leave them in the comments area and visit our blog ExcelDemy.
