`
Join In
Useful Tools
Consolidate each book into one sheet
Tools       2020/6/9 9:45:31          Author: FFCell
Views: 0

If we want to summarize data from multiple worksheets from each workbook to a new worksheet based on key fields, and this new worksheet will be placed in each original workbook, pls use this feature: Consolidate each book into one sheet.


For example, we want to summarize data based on Fruit from all worksheets of below each workbook to a new worksheet which will be added in each original workbook.


We choose the feature Consolidate each book into one sheet in Consolidate group, and then click Start button (upper right corner).


Step 1: Select excel files

We drag those two excel files and drop them to the blank area, and then click Next button.


Step 2: Select worksheets

All sheets means all sheets in the selected excel files will partcipate in consolidation.

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

Sheet 1 to 1 from left to right


If we want the specific sheets in the selected excel files to participate in consolidation, for example the sheet Februray, 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 Februrary) 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 participate in the consolidation, 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 2 parts to set: Consolidate range & Fields/columns.


In the part of Consolidate range, we need to set the table header and table footer. We can find that table header occupies 2 rows and table footer occupies 1 row in the selected worksheets.Thus we should set this part as Whole sheet: table header 2 rows, table footer 1 rows.


In the part of Fields/columns, we need to know that the fields are in row 2 in those selected worksheets.


And then click Get all fields, we will get all fields from the first worksheet in the first excel file.


And we want to summarize data based on Fruit, thus the key field is Fruit. In the All fileds list, we select Fruit and then click the first > button, Fruit will be added into the Key field list.


And the other fields are added into Summarize field list. The default calculation type is sum. We can right-click the summarize filed to change the calculation type. Pls take a look of below Gif present.


Step 4: Ready to consolidate

In this step, we need to name the new worksheet. The default new sheet name is Result, but you can input the new sheet name manually by yourself.

If you want the new worksheet to be placed in the first in each original workbook, pls set Location: in the first.

If you want the new worksheet to be placed in the last in each original workbook, pls set Location: in the last.


If the original workbook exists the same name sheet as the new sheet, you can tick Replace same name sheet. And the old sheet will be replaced by the new sheet. Let's click Start button. Consolidation completed in some seconds.


Finally, we are successful to summarize data from multiple worksheets from each workbook to a new worksheet based on key field (Fruit), and this new worksheet were placed in  the first of each original workbook. Now, let's open each original workbook and have a look.

Now, let's return to the Step 3, there are a Toggle button. Do you find it? What is the function?

To be exactly, there are two modes: Field name mode and Column letter mode. I have showed the Field name mode above. If the Field name mode is not suitable for your data, you can toggle to Column letter mode.


For example, you have mulitple worksheets, some field names are not the same completely, but they are in the same column. In this case, Filed name mode can not work, but Column letter mode can work.