Multiple Consolidation Ranges
Consolidating data is a useful way to combine data from different sources into one report. To create a Pivot Table, you can use data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures. However, you won’t get the same pivot table layout that you’d get from a single range. If possible, move your data to a single worksheet, or store it in a database, such as Microsoft Access, and you’ll have more flexibility in creating the pivot table. If combining your data isn’t an option, this pivot table tutorial explains the steps to create a pivot table from multiple consolidation ranges, describes the limitations, and suggests workaround solutions.
What is consolidating multiple ranges?
To summarize and report results from separate worksheet ranges, you can consolidate data from each separate worksheet range into a PivotTable report on a master worksheet. The separate worksheet ranges can be in the same workbook as the master worksheet or in a different workbook. When you consolidate data, you are assembling it so that you can more easily update and aggregate data regularly or as needed. The resulting consolidated PivotTable report can have the following fields in the PivotTable Field List, which you can add to the PivotTable report: Row, Column, and Value. In addition, the report can have up to four page filter fields, called Page1, Page2, Page3, and Page4.
Setting up the PivotTable and PivotChart Wizard
First of all, we setup a wizard named PivotTable and PivotChart Wizard in excel ribbon or Quick Access Toolbar which helps us to consolidate data easily. In this example I show you how to add this wizard in Quick Access Toolbar. For doing this, follow the below steps:
1. Click in File Menu and select option and then select “Quick Access Toolbar” option. A new window popup looks like:
2. Select “All Commands” from the dropdown menu of “Choose commands from” section.
3. Find “PivotTable and PivotChart Wizard” and select and then click on “Add>>” button. That’s all. Now you find a new option is included in your “Quick Access Toolbar” which is looks like:
Create a pivot table from multiple consolidation ranges
You can use the PivotTable and PivotChart Wizard to consolidate multiple ranges. In the wizard, you can choose between using no page fields, a single page field, or multiple page fields.
Consolidate data without using page fields
To combine the data from all the ranges and create a consolidation that does not have page fields, do the following:
1. Click a blank cell in the workbook where you actually want to consolidate
2. Click PivotTable and PivotChart Wizard option from the “Quick Access Toolbar”. A new window popup and select “Multiple Consolidation Ranges” and also select what kind of report do you want to create. which is looks like:
4. Click Next button. Again new window popup. In this example, I select PivotTable from the previous part and now select “Create a single page field for me”. You can actually change this option as you wish but right now follow me.
5. After click on next button a new window popup and provide you an option for selecting ranges by adding or deleting or modification as per your requirement.
6. Now select your required ranges and click add button for every selection. Here have a great feature, for the similar range, first select the range for your sheet and when you click on next sheet this wizard will automatically detect similar range for this sheet. I have three sheets named Database1, Database2 and Database3 and my ranges looks like below:
7. Click on next button and provide your required location for consolidation. I select cell A1.
My result is looks like below:
All are set, now you can change or modify pivot table as per your requirement. Note that, your sheets are located in filter section and column headings are in columns section etc. looks like below:
Limitations of Multiple Consolidation Ranges
In this example, Item is the first column in the data source, and the pivot table row heading shows the item names. Remaining fields are shown in the column area. You can change the function (e.g. SUM) that is being used by the data value, but it will use the same function on all these columns. The Pivot Table contains some meaningless data, such as sum of Date and columns full of zeros where the database columns contain text.
To get the best results, rearrange your database columns, so the most important column is at the far left. That column of data will become the Row values in the pivot table. If there are columns that you don’t want in the pivot table, move those to the far right in the source data. Then, do not include those columns when selecting the data ranges for the pivot table.