DEV Community

loading...

Merge All Worksheets of Same Name into One Worksheet in Excel 365!

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

In this guide, we will show you the simple steps to Merge All Worksheets of the Same Name into One Worksheet in Excel Office 365 using 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.

Merge All Worksheets of the Same Name Into One Worksheet:

To merge all worksheets of the same name across different workbooks into one worksheet, do as follows.

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

Kutools Plus Tab
Kutools Plus Tab

  • It will display a prompt box, you need to click the OK button.

Prompt box
Prompt box

  • Now, it will open the Combine Worksheets – Step 1 of 3 dialog box.
  • Combine Operations – In this section, you need to select the Combine all same name worksheets into one worksheet option.
  • Then, click the Next button.

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

  • It will open the Step 2 wizard , where you need to select the workbooks and worksheets that you want to combine.

Step 2 wizard
Step 2 wizard

Configure the settings as below:

#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) 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.

#3) 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

#4) 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 text box. Then, fill 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.

#5) Same range option

  • Using this option, you can select the specified range in one worksheet and select the same range from all other sheets in the workbook automatically.

Same range option
Same range option

#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

  • It will go to the Step 3 wizard. In this dialog box, you need to specify the operations that you need.
  • Then, click the Finish button.

Step 3 wizard
Step 3 wizard

  • Now, it will open the Specify the file name and location for the combined workbook dialog box , where you need to select a folder to save the combined workbook.

Click Save button
Click Save button

  • Click the Save button.
  • The new workbook with the information of original workbooks is created as shown in the below image.

Output
Output

  • Output file link – You can click this link to view the combined workbook directly.
  • Then, all worksheets are combined into one worksheet according to your settings.

Result
Result

Closure:

In this guide, you can easily understand the steps to Merge All Worksheets of the Same Name into One Worksheet in Excel Office 365 using Kutools. Kindly, share your valuable queries/worthwhile feedback with us. Thanks for visiting Geek Excel. Keep Learning!

Keep Reading:

Discussion (0)

pic
Editor guide