DEV Community

loading...

Combine Worksheets/Workbooks/Csv Files into One Workbook in Excel 365!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・5 min read

Here, we will describe the step-by-step instructions to Combine Worksheets/Workbooks/CSV Files into One Workbook in Excel Office 365 with Kutools. Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Note: kutools for Excel has more than 180 features which are used to complete the difficult task with several clicks. If you want to use Kutools, you need to install it from Excel’s official website.

Combine Multiple Worksheets/Workbooks into One Workbook:

To combine worksheets/workbooks in Excel, then follow the below instructions.

  • Let’s consider the example workbooks 1 , 2 , and 3. We are going to merge all worksheets of those workbooks into one workbook.

Multiple Workbooks
Multiple Workbooks

  • Go to the Kutools Plus Tab, select the Combine option.

Kutools Plus Tab
Kutools Plus Tab

  • Then, it will open the Kutools for Excel Dialog Box. You need to click the OK button to proceed with the process.

Prompt box
Prompt box

  • Then, it will display the Combine Worksheets – Step 1 of 3 wizard.
  • *Combine Operations – * In this section, you need to select *Combine multiple worksheets from workbooks into one workbook * option.
  • Then, you need to click the Next button.

Click Next button
Click Next button

  • Then, it will display the Combine Worksheets – Step 2 of 3 wizard.
  • You can see all the opened workbooks and corresponding worksheets are listed in the Workbook list and Worksheet list boxes.
  • If you want to add workbooks, you can use the Add>Files or Folder button to select the workbook.
  • After adding the specifying workbooks and worksheets that you want to merge, then click the Next button to move to the next step.

Combine Worksheets - Step 2 of 3
Combine Worksheets – Step 2 of 3

Note:

#1) Add workbooks to merge

  • In the Add drop-down list, you can click the Files(s) button to add one workbook or multiple workbooks to merge.
  • Folder – It will add all workbooks of the folder into the workbook list to merge.
  • OneDrive File(s) or OneDrive Folder – This will add one or multiple OneDrive files from one folder or different folders as you need.

Add option
Add option

#2) Remove workbooks from the workbook list box

  • Workbook list – You can delete a single workbook from the list by using the X button.

Workbook list
Workbook list

  • Remove all – It will remove all the workbooks from the workbook list.

Remove all button
Remove all button

#3) Open workbooks which have been encrypted with the password

  • Password – This option is used to combine the workbooks which have been encrypted with passwords.
  • Password Manager Dialog box – Click the Add button, enter the workbook password into the Password text box. Then, fill in the Label and click the OK button.
  • Repeat the above step until the workbooks passwords are added to the manager, then click the OK button to close the window.

#4) Save/Access one scenario of your operation

  • You can save the settings of Step 2 for future use. You need to click the Save option under the Scenario drop-down list.
  • Then, you need to give the name for the scenario in the popping-out dialog box.

Scenario option
Scenario option

#5) Sort all workbooks or worksheets in the workbook

  • Sort drop-down list – It is used to sort all workbooks or worksheets in the list box.

"<yoastmark

#6) Same Sheet option

  • With this option, you can select the specific sheet across multiple workbooks that contain a specific sheet name.

Same Sheet option
Same Sheet option

  • Now, it will open the Combine Worksheets – Step 3 of 3 wizard.
  • In this dialog box, you need to configure as following options.
  • Blank worksheet – If your workbooks contain blank or empty worksheets, you can keep or skip the blank worksheets.
  • Insert worksheet information – It will insert the workbook information at the first row of each worksheet as a comment in the combined workbook.
  • Rename worksheet names – You need to check the By inserting workbook name ** option. Then, **select In front of original worksheet name or After original worksheet name option as you wish.
  • Separator – You can also give the separator in this box.
  • Finally, you need to click the Finish button.

Combine Worksheets - Step 3 of 3
Combine Worksheets – Step 3 of 3

  • After combining the worksheets, it will open the Specify the file name and location for the combined workbook Dialog Box.
  • You need to specify the file name and location for the combined file. Then, click the Save button.

Click the Save button
Click the Save button

  • At last, a new workbook is created and the status of the combined workbooks is listed.

Workbook list
Workbook list

  • After that, you can also click the Output file link to open the combined workbook directly.
  • Then, the new workbook contains detailed information about the new workbook as shown in the below screenshot.

Result
Result

Verdict:

In the above article, we described the step-by-step instructions to Combine Worksheets/Workbooks/CSV Files into One Workbook in Excel Office 365 with Kutools ** with the best practical examples. Share your valuable **queries/suggestions in the below comment section. Thanks for visiting Geek Excel. Keep Learning!

Discussion

pic
Editor guide