Quickly merge / combine worksheets or workbooks into one workbook in Excel

When it comes to merge or combine multiple worksheets or workbooks into one workbook, most of us will think about copying and pasting the worksheets into one workbook. But if there are lots of worksheets need to be merged by such way, it will waste much time. Kutools for Excel augments Excel this powerful feature – Combine Worksheets. With this utility you can easily:

Merge / combine multiple worksheets or workbooks into one workbook

Merge / combine specific worksheets from different workbooks into one workbook

Merge / combine all workbooks from a directory into one workbook

Merge / combine all worksheets of same name across workbooks into one workbook

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Click Enterprise >> Combine. See screenshots:


arrow blue right bubbleMerge / combine multiple worksheets or workbooks into one workbook

Supposing you have Workbook A, Workbook B and Workbook C, and you want to merge or combine all worksheets of those workbooks into one workbook. You can get it done as follows:

1. Please apply the Combine Worksheets by clicking Enterprise > Combine. In the Combine Worksheets wizard, please select Combine multiple worksheets from workbooks into one workbook option, and then click Next button. See screenshot:

2. Please click Add > File… or Folder… button to add the workbooks that you want to merge. See screenshot:

Tips:

A. Add workbooks to merge

Click File… under Add button, you can add one workbook or multiple workbooks into Workbook list to merge. If you click Folder… and specify a folder, it will automatically add all workbooks of the folder into Workbook list.

B. Save or access one scenario of your operation:

If you want to save the settings of Step 2 in Combine Worksheets for future operations, you can save a screnario of the settings by clicking Scenario > Save… button. By doing so, you don’t need to add workbooks or specify the worksheets again and again in the future, you just need to open the scenario item you have saved.

C. If you want to delete a workbook from the Workbook list, please click on the workbook and then click button, it will remove the workbook from the list immediately.

D. What’s the Same sheet button? At the button of the Worksheet list, there is a Same sheet button. With this button, you can easily select all worksheets with same worksheet name across all the checked workbooks in the Workbook list. For example, there are 5 workbooks that have been checked in the Workbook list, if you click on the Sheet4 as shown in the below screenshot, it will immediately select all worksheets with same worksheet name across the 5 checked workbooks after clicking Same sheet button. See screenshots:

There are only 2 workbooks (Book5 and Book3) contains the same name worksheet (Sheet4), so you will have the results as follows:

3. After adding and specifying the workbooks and worksheets you want to combine or merge, please click the Next button. In the Step 3, please configure following options:

A. Rename worksheet names after merging:

If you want to insert or add the original workbook name into the worksheet names after merging, please check By inserting workbook name option and specify the location where you want to insert the workbook name. You can insert it before or after the original worksheet name. At the same time, you can specify a separator between the workbook name and worksheet name.

If you don't want to insert or add the workbook name into worksheet names, please uncheck By inserting workbook name option.

B. To merge blank / empty worksheets or not:

If your workbooks contain blank or empty worksheets, you can keep or skip the blank worksheets.

4. Then click Finish button to merge the workbooks, and it will pop up a window to remind you to save the scenario or not if you have not saved the scenario in the Step 2. See screenshots:

5. Click OK to save this scenario. As you can see, multiple worksheets or workbooks have been combined into a new workbook. In the new workbook, there will be a worksheet which names Kutools, and it contains detailed information about the new workbook as shown in the below screenshot.


arrow blue right bubble Merge / combine specific worksheets from different workbooks into one workbook

Supposing you have Workbook A, Workbook B and Workbook C, and you want to merge or combine 2 worksheets of Workbook A, 3 worksheets of Workbook B and 1 worksheet of Wordbook C into one workbook. You can do as follows:

1. Most of the steps or configure settings will be as similar as Merge / combine multiple worksheets or workbooks into one workbook, please refer to Tips of Merge / combine multiple worksheets or workbooks into one workbook.

2. For merging or combine specific worksheets from different workbooks into one workbook operation, the major operation or most important part will in the Step 2 to specify the worksheets which you want to merge or combine. Before going to the Step 2, you need to click Enterprise > Combine > Combine multiple worksheets from workbooks into one workbook, after clicking Next button, please specify the specific worksheets from different workbooks you want to merge or combine as follows:

For example, to merge or combine 2 worksheets of Workbook A, 3 worksheets of Workbook B and 1 worksheet of Wordbook C into one workbook.

3 worksheets of Workbook B:

1 worksheets of Workbook C:

For the other settings of Next step, please refer to Tips of Merge / combine multiple worksheets or workbooks into one workbook.

After clicking Finish button, you will see the results as shown in the below screenshot.


arrow blue right bubble Merge / combine all workbooks from a directory into one workbook

Supposing you have Workbooks A, Workbook B , Workbook C and other workbooks locating at one file folder, and you want to merge all workbooks (Excel files) of the same file folder into one workbook. You can do as follows:

1. Please click Enterprise > Combine > Combine multiple worksheets from workbooks into one workbook, after clicking Next button, you can add all workbooks from a directory (file folder) to merge or combine in the Step 2 as shown in the below screenshots, and for the other settings, please try to find more information from Tips of Merge / combine multiple worksheets or workbooks into one workbook.



All workbooks of this folder will be listed in the Workbook list.

You can just click Finish button to merge or combine all workbooks that have been just added from a folder. For the other settings of Next step, please refer to Merge / combine multiple worksheets or workbooks into one workbook.

All workbooks of the same folder will be merged or combined into one workbook. Note: you can add workbooks from multiple folders, not just one folder.

arrow blue right bubble Merge / combine all worksheets of same name across workbooks into one workbook

If you want to merge or combine all same name worksheets from different workbooks into one workbook, for example, to merge or combine all the worksheets with worksheet name “Sheet2” from Workbook A, Workbook C and Workbook D into one workbook, you can get it done as follows:

1. Please click Enterprise > Combine > Combine multiple worksheets from workbooks into one workbook, after clicking Next button, you can easily select all same name worksheet as shown in the below screenshots.


As we are going to merge or combine all the worksheets with worksheet name “Sheet2”, so we click on the Sheet2 as shown in the above screenshot, and the click Same sheet button to quickly select all worksheets with worksheet name “Sheet2” across all checked workbooks (Workbook A, Workbook B and Workbook C).

After clicking Same sheet button, Sheet2 of Workbook A is checked or selected as shown in the below screenshot.

Sheet2 of Workbook B is checked or selected as shown in the below screenshot.

Note: With the Same sheet button, you don’t need to manually select or checked all the worksheets with same worksheet name one by one when you need to quickly merge or combine all same name worksheets of different workbooks into one workbook in Excel.

After clicking Finish button, you will see the results as shown in the below screenshot. The rest setting of Next step, please refer to Tips of Merge / combine multiple worksheets or workbooks into one workbook.

Do you want to know how to merge or combine all same name worksheets from different workbooks into one worksheet? Please refer to Quickly merge / combine all worksheets of same name across workbooks into one worksheet.


arrow blue right bubble This is just one of 120 powerful features of Kutools for Excel

It includes more than 120 advaned functions and tools for Excel 2007, 2010 and 2013 (includes Office 365).



btn read more     btn download     btn purchase

Add comment


Security code
Refresh