`
Join In
Useful Tools
Combine many books into one book
Tools       2020/6/8 16:52:32          Author: FFCell
Views: 0

If we want to combine multiple workbooks into one workbook or many excel files into one excel file, in detail, to combine multiple worksheets from multiple workbooks into one workbook, it is the right choice to use the feature: Combine many books into one book.


For example, we want to combine below two workbooks into one workbook.

And let's have a look what is the content in these two workbooks:

Thus, we know that the workbook Store A.xlsx has two worksheets whose names are January and February.

The workbook Store B.xlsx also has two worksheets whose names are January and February.

Now, let's combine these two workbooks into one workbook.


We choose the feature Combine many books into one book in Combine group, and then click the Start button (upper right corner).


Step 1: Select excel files

We can drag these two excel files and drop them to the blank area, or click Add files button to select those excel files you want to combine.

If there are some excel files you do not need, pls select them and then click Remove button. Click Remove all button will remove all excel files.

If we want to see the path of the excel files, pls tick Show path.

After selecting the excel files we need, and then click Next button.


Setp 2: Select worksheets

All sheets means all sheets in the selected excel files will be copied to a new excel file.

If we only want to copy the first sheet in each selected excel files, we can choose the next option and set it as :

Sheet 1 to 1 from left to right


If we want to copy specific sheets in the selected excel files to a new excel file, for example only copy sheet February, pls choose this option:Specific sheets (MultiSelect)

And we will get all sheets' names from the first excel file by default, and then tick the specific sheet (sheet February ) you want.

If the first excel file does not include sheet February, we can click Get All , and then we will get all sheets' names from all selected excel files. But it will take a long time.

It is better for us to click the Input button and then input the sheet name manually.

If we don't want the hidden sheets or blank sheets to be copied to the new excel file, pls tick Skip hidden sheets and Skip blank sheets. In Step 2, we decide to choose All sheets, and then click Next button.


Step 3: Data options

In this step, we have 3 parts to set: Copy range, Paste options and Advanced options.

In the part of Copy range:

Whole sheet means it will copy all contents in the sheets to the new workbook.

If we only want to copy specific range of the selected sheets, for example range A2:E7, pls choose the next one, and set it as Range: From cell: A2 to cell: E7.

If the data region in selected sheets begin from the third row, but they end in different row, pls choose the last one and set it as Range: From row from top 3 to row from bottom 1 of data region


In the part of Paste options:

Paste all (values, formulas, forat etc.) means to copy all information including values, formuls, format, notes etc.

Paste values only means to copy the values only in selected sheets to the new workbook.

Paste formulas only means to copy the formulas only.

Paste values & format means to copy the values and format.

Paste formulas & format means to copy the formulas and format.

Create links to source data means that the result data will update automatically if original data changes.


In the part of Advanced options:

Preserve row height & column width will keep the row height & column width of result data the same as it in original sheet.

Preserve comments will copy the comments to the result workbook.

After setting the data options, then click Next button.


Step 4: Ready to combine

In this step, we need to set the new sheet name in the result workbook, we can do it by clicking Naming Convention button in the right side. What is Naming Convention, how to use it, pls click here.

And then we can click Browse button to set the saving path of the result workbook.


Now, let's click Start button to combine. Combination completed in some seconds.


Finally, we get a result workbook whose name is Result.xlsx. We open it and have a look.


Fast and convenient.

Pretty cool.