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 same or different ranges from different worksheets into one worksheet

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Click Kutools Plus >> Combine. See screenshots:

shot combine 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 utility in your Excel by clicking Kutools Plus > Combine, and a warning message is popped out, see screenshot:

2. Then click OK, and in the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one worksheet option, see screenshot:
shot combine 2

3. Then click Next button, and in the following Combine Worksheets wizard, please specify the workbooks or worksheets that you want to combine, see screenshot:
shot combine 3

Tips 1:

A. Add workbooks or CSV files or OneDrive filesas you need to merge

Click File(s) 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…, it will automatically add all workbooks in a specific folder into Workbook list to merge.
If you click OneDrive File(s) or OneDrive Folder..., it will automatically add one or multiple OneDrive files from one folder or different folders as you need.
shot combine 4

B. Sort all workbooks or worksheets in the Workbook or worksheet list

Specify a sorting condition in the Sort drop-down list to sort all workbooks or worksheets in the list box. For example, select Name in the Sort drop-down list, all workbooks or worksheets in the list box are sorted by name automatically.
shot combine 5

C. Save or access one scenario of your operation:

If you want to save the settings of Step 3 in Combine Worksheets for future operations, you can save a scenario 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. At the same time, you can go to the Manage Scenario dialog box for renaming or deleting the scenarios as you need.

D. If you want to delete a workbook from the Workbook list, please select the workbook and then click the button. For deleting all workbooks in the workbook list, you just need to click button to remove them at once.

E. Open and deal with the workbooks which have been encrypted by password correctly:

If the workbooks have been encrypted with passwords that you want to combine, please click the Password button, in the Password Manager dialog box, click Add button to add the workbooks' passwords one by one that you want to combine, see screenshot:

shot combine 6

F. 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$5 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 7

G. 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 3 workbooks that have been checked in the Workbook list, if you click on the Sheet3 as shown in the below screenshot, it will immediately select all worksheets with same worksheet name across the 3 checked workbooks after clicking Same sheet button. See screenshots:

shot combine 8

4. After configuring in the Step 3, please click Next button. You will go into the last step, please choose the specific operations you need, see screenshot:

shot combine 9

Tips 2:

A. Combine by row and Combine by column:

  • Combine by row: Each combined data from multiple worksheets is added to the last row of the last combined data;
  • Combine by column: Each combined data from multiple worksheets is added to the last column of the last combined data.

B. What is the title and total row numbers?

If your data has the title and total rows as shown in the below screenshot:

If you specify the Number of header and total rows as value “0” for the above data, it will have the results as shown in the below screenshot. It will keep all the first and last rows of each worksheet in the final worksheet.

If you specify the Number of header and total rows as value “1”, the results will be as shown in the below screenshot. It will only keep the first header row of the first worksheet and delete all the header and total rows of other worksheets after combining.

C. Insert original worksheet information into the new combined worksheet:

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 comment

D. Specify the paste options when combining the worksheets:

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.

5. Click Finish button to start combining, after combining the sheets, a Specify the file name and location for the combined workbook window is popped out, you can specify a file name and location for your combined file, and then click the Save button. See screenshot:

Note: If you haven't saved the workbook and worksheet settings as a scenario in step 3, a Kutools for Excel window will pop up to remind you to save the scenario. Click the Yes button and name it in the next Save Scenario dialog. Otherwise, click the No button. See screenshot:

6. And a new workbook with the information of the original workbooks is created, in this workbook, the hyperlink of the new combined workbook is output as well, you can click it to open the combined workbook. See screenshot:

7. Then all worksheets of workbooks will be combined into one worksheet according to your settings. See screenshot:

The data in the worksheets are combined row by row:

The data in the worksheets are combined column by column:


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 Kutools Plus > 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 3, 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), please click on the Sheet1 and then click Same sheet button. All the worksheets of same name (Sheet1) in workbooks (A, B, C) are selected as shown in the below screenshot:

In the Step 3, please configure the settings according to Step 4 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 same or different ranges from different worksheets into one worksheet

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

1. Please apply the Combine Worksheets utility in your Excel by clicking Kutools Plus > 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 3, please refer to the Tips section of Combine worksheets from workbooks into one worksheet. Specify the worksheets and data ranges that you want to combine from the workbook and worksheet list box.(To combine the same range from multiple worksheets, you just need to select one range in the worksheet list, and then click Same range button to select all the same range from multiple sheets at once.)

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

Note: In this step, please configure the settings according to Step 3 of Combine worksheets from workbooks into one worksheet

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.

Note: If you think the Combine feature is useful, you can click shot share buttonbutton to share it to your friends.
shot share


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

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 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 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

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

Designed for Excel(Office) 2019, 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 · 3 years 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 · 3 years 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 · 3 years 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 jaychivo@extendoffice.com.