Join In
Useful Tools
Consolidate many books into one sheet
Tools       2020/6/9 9:43:55          Author: FFCell
Views: 0

If we want to summarize data from multiple worksheets of multiple workbooks to a new worksheet based on key fields, it is the right choice to use this feature: Consolidate many books into one sheet.

For example, we want to summarize data based on Fruit from all worksheets of below two workbooks to a new worksheet.

We choose the feature Consolidate many books 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 can click Browse button to change the saving path of the result workbook if we do not like the default saving path. And then click Start button. Some seconds later, consolidation completed.

Finally, we get a workbook whose name is Result.xlsx and there are only one worksheet in this workbook. Let's open it and have a look. We are successful to summarize data based on Fruit from all worksheets of those two workbooks to a new worksheet

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.