Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

Quickly combine multiple worksheets / ranges from workbooks into one worksheet

Supposing you want to merge or combine multiple worksheets into one worksheet, normally, you can copy and paste the worksheets into the master worksheet one by one, but this way will be tedious and time-consuming. With Kutools for Excel’s Combine utility, you can quickly:

Combine worksheets from workbooks into one worksheet

Combine worksheets of same name into one worksheet

Combine ranges from different worksheets into one worksheet

Combine same ranges of all worksheets into one worksheet

Combine different ranges from different worksheets into one worksheet


Click Enterprise >> Combine. See screenshots:

shot combine sheets into one 1

Combine worksheets from workbooks into one worksheet

If you want to combine or merge multiple worksheets from different workbooks into one worksheet in Excel, you can quickly get it done as follows:

1. Please apply the Combine Worksheets utility in your Excel by clicking Enterprise > Combine > Combine multiple worksheets from workbooks into one worksheet.

2. In the Step 2 (See screenshot), please specify the workbooks or worksheets to be combined.

Tips:

A. Add workbooks or CSV files as you need to merge

Click File… under Add button, you can add one workbook or multiple workbooks into Workbook list to merge. For adding CSV files, you need to specify the file type as (*.csv) or (*.xls; *.csv) in the Open dialog box.
If you click Folder… and specify a folder, it will automatically add all workbooks of the folder into Workbook list to merge.

shot combine into one sheet 5

B. Sort all workbooks in the Workbook list

Specify a sorting condition in the Sort drop-down list to sort all workbooks in the Workbook list. For example, select Name in the Sort drop-down list, all workbooks in the Workbook list are sorted by name automatically.

C. 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 on the lower-left corner of the dialog box. 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, then all the workbooks will be listed in the Workbook list.

shot combine into one sheet 6

D. If you want to delete a workbook from the Workbook list, please select the workbook and then click the shot combine into one sheet 7 button. Then the selected workbook will be removed from the Workbook list immediately.

E. What’s Same range button? It will automatically specify each worksheet of all checked workbooks in the Workbook list to have the same range as the worksheet you selected. For example, The Sheet1 of Workbook A has a range $A$1:$C$6 as shown in the below screenshot, if you first click on Sheet1 and then click Same range button, you will see the range of Sheet2 of Workbook A is changed to the same range as Sheet1, and each worksheet of checked workbooks in the Workbook list will also have the same range as Sheet1 of Workbook A. See screenshots:

shot combine into one sheet 8

shot combine into one sheet 9

F. What’s the Same sheet button? You can see there is a Same sheet button in the dialog box. 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) contain the same name worksheet (Sheet4), so you will have the results as follows:

3. After configuring in the Step 2, please click Next button. You will go into the Step 3. See screenshot:

shot combine into one sheet 14

Tips 2:

What is the Title row numbers? If your data has the title row as shown in the below screenshot:

If you specify the Title row numbers as value “0” for the above data, it will have the results as shown in the below screenshot. It will keep all the first rows of each worksheet in the final worksheet.

If you specify the Title row numbers as value “1”, the results will be as shown in the below screenshot. It will use one row as the title row and delete the first row of all the other worksheets after combining.

If you want to insert the worksheet information, you can insert the worksheet information in the first row of each range as well as formatting as comment styles.

Insert worksheet information in a new row
Insert worksheet information as comments

The other options in this step are easy for you to understand except the Paste link to source data option. This option will update automatically the combined (data) worksheets when the source data are changed.

You can exclude or include the filter data with checking or unchecking the Exclude filtered data box.

4. Click Finish button to start combining, then all worksheets of workbooks will be combined into one worksheet according to your settings. See screenshot:


Combine worksheets of same name into one worksheet

If you want to combine or merge worksheets of same name into one worksheet, you can quickly get it done as follows:

1. Please apply the Combine Worksheets utility in your Excel by clicking Enterprise > Combine > Combine multiple worksheets from workbooks into one worksheet.

2. In the Step 2, please add the workbooks that you want to combine worksheets of same into one worksheet. For more information about the settings of Step 2, please refer to the Tips section of Combine worksheets from workbooks into one worksheet.

For example, if you want to combine worksheets of same name (Sheet1) into one worksheet from workbooks (A, B, C, D and E), please click on the Sheet1 and then click Same sheet button. All the worksheets of same name (Sheet1) in workbooks (A, B, C, D and E) are selected as shown in the below screenshot:

In the Step 3, please configure the settings according to Step 3 of Combine worksheets from workbooks into one worksheet.

3. After clicking Finish button, all worksheets of same name have been combined into one worksheet as shown in the following screenshot.


Combine ranges from different worksheets into one worksheet

If you want to combine or merge ranges from different worksheets into one worksheet, you can quickly get it done as follows:

1. Please apply the Combine Worksheets utility in your Excel by clicking Enterprise > Combine > Combine multiple worksheets from workbooks into one worksheet.

2. In the Step 2, please add the workbooks that you want to combine ranges of different worksheets into one worksheet. For more information about the settings of Step 2, please refer to the Tips section of Combine worksheets from workbooks into one worksheet. For example, if you want to combine range ($A$1:$C$6) of Sheet1 in the Workbook A and the range ($A$1:$D$7) of Sheet2 in the Workbook B in to one worksheet as shown in the following screenshots.


In the Step 3, please configure the settings according to Step 3 of Combine worksheets from workbooks into one worksheet.

3. After clicking Finish button, ranges of different worksheets have been combined into one worksheet as shown in the following screenshot.


Combine same ranges of all worksheets into one worksheet

If you want to combine same ranges of all worksheets into one worksheet, you can quickly get it done as follows:

1. Please apply the Combine Worksheets utility in your Excel by clicking Enterprise > Combine > Combine multiple worksheets from workbooks into one worksheet.

2. In the Step 2, please add the workbooks that you want to combine same ranges of all worksheets into one worksheet. For more information about the settings of Step 2, please refer to the Tips section of Combine worksheets from workbooks into one worksheet.
For example, if you want to combine the range ($A$1:$C$9) of all worksheets across different workbooks, please first specify a range ($A$1:$C$9) in one worksheet as follows:

And then click Same range button at the bottom of the Worksheet list, then all worksheets of workbooks in the Workbook list have the same range ($A$1:$C$9) as shown in the following screenshots.

In the Step 3, please configure the settings according to Step 3 of Combine worksheets from workbooks into one worksheet.

3. After clicking Finish button, same range ($A$1:$C$9) of different worksheets have been combined into one worksheet as shown in the following screenshot.


Combine different ranges from different worksheets into one worksheet

If you want to combine different ranges from different worksheets into one worksheet, you can quickly get it done as follows:

1. Please apply the Combine Worksheets utility in your Excel by clicking Enterprise > Combine > Combine multiple worksheets from workbooks into one worksheet.

2. In the Step 2, please add the workbooks that you want to combine different ranges of different worksheets into one worksheet. For more information about the settings of Step 2, please refer to the Tips section of Combine worksheets from workbooks into one worksheet.
For example, if you want to combine the range ($G$10:$I$15) of Sheet1 in the Workbook A and the range ($U$11:$W$16) of Sheet1 in the Workbook B into one worksheet as shown in the following screenshots.

In the Step 3, please configure the settings according to Step 3 of Combine worksheets from workbooks into one worksheet.

3. After clicking Finish button, the range ($G$10:$I$15) of Sheet1 in the Workbook A and the range ($U$11:$W$16) of Sheet1 in the Workbook B have been combined into one worksheet as shown in the following screenshot.

If you want to combine worksheets of same name into one worksheet along with the other different worksheets into the same workbook, please refer to Quickly merge / combine all worksheets of same name across workbooks into one worksheet.


Demo: Quickly combine multiple worksheets / ranges from workbooks into one worksheet

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 200 Advanced Functions for Excel 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 200 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tim · 7 months ago
    I got Kutools 16 with excel 2013. When I try to load multiple workbooks to combine, not even a single workbook comes up in the list. I tried removing and uninstalling Kutools, but didn't fix. What is wrong?
    • To post as a guest, your comment is unpublished.
      Darshan · 5 months ago
      I have the exact same issue. Did you resolve this? Really need this great feature to work with massive excel workbooks i have been handed.

      [quote name="Tim"]I got Kutools 16 with excel 2013. When I try to load multiple workbooks to combine, not even a single workbook comes up in the list. I tried removing and uninstalling Kutools, but didn't fix. What is wrong?[/quote]
    • To post as a guest, your comment is unpublished.
      Admin_jay · 6 months ago
      [quote name="Tim"]I got Kutools 16 with excel 2013. When I try to load multiple workbooks to combine, not even a single workbook comes up in the list. I tried removing and uninstalling Kutools, but didn't fix. What is wrong?[/quote]

      Can you please create a sample data in new workbook and try it again?

      If the problem persists with the sample workbook, please send it to me.

      You can contact me via .