Consolidator

With this tool, you can consolidate several worksbooks and/or sheets into one single sheet.
A wizard will take you step-by-step throughout the consolidation process.
You can either create a new consolidation model and save it, or work with existing, previously saved consolidation models you want to reuse.
You can download the example used in this Help page from this link:

Download example

1. As the Wizard dialog pops up, select New Consolidation.

Consolidator Wizard Step 1 of 4

We are going to explain now what each option does:

A.- Consolidate the selected sheets from one or several workbooks (opened or closed) in one sheet.

You can consolidate several sheets from several workbooks -whether open or closed. The resulting consolidation is shown in one single workbook and one single sheet, as shown in the following figure:

B.- Consolidate the selected sheets from several workbooks (opened or closed) in one sheet per workbook.

You can consolidate selected sheets in different workbooks into one single workbook. The results are shown in one sheet with the name (or part of the name) of each workbook containing the sheets to be consolidated, as shown in the following figure:

C.- Consolidate sheets with the same name in several workbooks (opened or closed) in sheet's groups with the same name.

You can consolidate sheets with the same name in several workbooks -whether opened or closed. The results are shown in one single workbook with sheets with same name being consolidated into one single sheet, as seen in the next image:

+

D.- Copy the sheets of several workbooks in just one workbook.

You can copy sheets from several workbooks -either closed or open- in one single workbook.
Workbooks from different Excel versions are validated, since Microsoft Excel 2007 has a different number of rows and columns from prior versions.

2. Select all sheets to be consolidated (all except for 'Indications', which contain the indications for this example).

3. Click on Next .
Now select the range containing the data to consolidate, the mathematical function used for consolidation, and the target range where the results are to be pasted.

 

 

You may save this consolidation model for future use. For further details, please refer to Working with existing models.

4. Click on Consolidate -and that's it!
You can see the result starting on cell A24.