`
Join In
Useful Tools
Consolidate data with same cell address into one sheet
Tools       2020/6/9 9:49:22          Author: FFCell
Views: 0

If we want to summarize data with same cell address from multiple worksheets of multiple workbooks to a new worksheet, it is the right choice to use this feature: Consolidate data with same cell address into one sheet.


For example, we want to summarize data with same cell address (B2:B8) from all worksheets of below two workbooks to a new worksheet.


We choose the feature Consolidate data with same cell address 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 statement, 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 statement) you want.


If the first excel file does not include sheet February statement, 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 need to set Consolidate range and Calculation type.We need to input the cell ranges B2:B8 into the Range list manually. As to the Calculation type, we can set numbers Concatenated by plus signs and Text Concatenated by commas. And then click Next button.


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. We are successful to summarize data with same cell address (B2:B8) from multiple worksheets of multiple workbooks to a new worksheet. Let's open it and have a look.