Pivot Table -- Multiple Consolidation Ranges

Create a Pivot Table using data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures.

April 12th - 18th: Free webinar, Mastering Pivot Tables -- Learn how to set up data and create a Pivot Table in less than 3 minutes. Create an interactive dashboard with Slicers & Pivot Charts. Show key business metrics, and much more. Click here to pick a date and time: Free PivotTable Webinar

Create a Pivot Table from Data on Different Sheets

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.

table structure for multiple consolidation

However, you won't get the same pivot table layout that you'd get from a single range, as you can see in the screen shot below.

pivot table from multiple consolidation ranges

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.

Create a pivot table from multiple consolidation ranges

  1. To open the PivotTable and PivotChart Wizard, select any cell on a worksheet, then press Alt+D, then press P. That shortcut is used because in older versions of Excel, the wizard was listed on the Data menu, as the PivotTable and PivotChart Report command.
  2. Click Multiple consolidation ranges, then click Next

    PivotTable and PivotChart Wizard

  3. Click "I will create the Page Fields", then click Next
  4. Select each range, and click Add
  5. select ranges for multiple consolidation

  6. Click 1 as the number of Page Fields
  7. In the list of ranges, select the first range, and type an item label for that range in the page fields
  8. Repeat for the remaining ranges. In the screen shot below, the range on the West sheet is selected, and the item label, "West" has been entered for that range.
  9. page field item labels for multiple consolidation

  10. Click Next
  11. Select a location for the PivotTable, then click Finish

A pivot table appears on the worksheet, with the first field in the Row area, and all of the other fields from the source data in the Values area, showing a Count.

pivot table from multiple consolidation ranges

Clean Up the Multiple Consolidation Pivot Table

All of the fields from the source data are included in the multiple consolidation pivot table, so you can remove some of them, and make a few other changes.

Remove fields that don't contain meaningful data

In this example, the Colour, Date, Price and Rep fields contain text, or numbers that are meaningless in this report, so they will be removed.

  1. Click the drop down arrow in the Column Labels heading
  2. Remove the check marks for fields that you want to remove.
  3. Click OK

    remove values from multiple consolidation pivot table

Change the Value Field Calculation

By default, the Values will show as Count, and you can change that to Sum, or another calculation.

NOTE: This will affect all of the Values -- they cannot be changed separately.

  1. Right-click one of the Values
  2. Point to Summarize Values By, and click on Sum.

change the value field calculation

Remove the Grand Total for Rows

The Grand Total for Rows is meaningless in this report, because it is showing the total for unrelated items, so it should be removed..

  1. Right-click on the heading for the Grand Total for Rows
  2. Click Remove Grand Total.

remove grand total for rows

Change the Labels

In the pivot tables, generic fields are created -- Row, Column, Value and Page1. You can rename those fields, to make the pivot table easier to understand.

  1. Click on any label in the pivot table, and type a new label, then press Enter
  2. For example, click on the Page1 label, type Region, and press Enter

The labels have been changed in the screen shot shown below. The Column Labels heading was replaced by a space character.

remove values from multiple consolidation pivot table

Change the Layout

By default, the pivot table has the Compact Report Layout, and you can change that to Outline, so each Row field will be in a separate column. Then, move the Page field into the Row area, above the existing Row field.

  1. Select any cell in the pivot table
  2. On the Ribbon, under PivotTable Tools, click the Design tab.
  3. In the Layout group, click Report Layout, then click Outline Form
  4. In the PivotTable Field List, drag the Page1 field from the Filters area, into the Row area, above the existing Row field.
  5. Change the Row Field to Item, now that it is in a separate column.

remove values from multiple consolidation pivot table

Video: Create a Pivot Table from Multiple Sheets

To see the steps for creating a pivot table from data on different sheets, what this multiple consolidation ranges pivot table video tutorial.

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.

  • The Pivot Table contains some meaningless data, such as sum of Date and columns full of zeros where the database columns contain text. Remove those fields, as described in the section on Clean Up the Pivot Table, above.
  • values changed to Sum

  • 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 first column in the source data is always added to the Row area in the pivot table.
    • 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.

Alternatives to Multiple Consolidation Ranges

To avoid the limitations of multiple consolidation ranges, you can try one of the following alternatives.

Video: Combine Tables With Power Query

If you have a version of Excel that supports Microsoft's Power Query add-in, you can use it to combine the data in two or more tables. The tables can be in the same workbook, or in different files.

The tables can have different structures, and should have some columns with identical headings, in which the data can be combined. In this example, the East and West region data will be combined, and one column is unique in each table.

combine tables in Power Query

To follow this video tutorial, download the sample file from the section below.

Manually Create a Union Query

If you can't combine your data on a single worksheet, another solution is to create named ranges in an Excel file, and use Microsoft Query (MS Query) to combine the data. There are sample files here:

Then, use the Union query (full outer join) result as the pivot table's source data.

pivot union query

With this solution, you'll end up with a normal pivot table, with none of the limitations. However, it's a bit tedious to set up, especially if you have more than a couple of tables.

You can read more about MS Queries here:

Use a Macro to Create a Union Query

Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Héctor Miguel Orozco Diaz.

Before you use the sample code, replace the sample sheet names with the sheet names in your workbook.

For example, if your sheet names are "East" and "West", change this line of code:

arrSheets = Array ("Ontario", "Alberta")

to this

arrSheets = Array ("East", "West")

pivot multiple sheets code

In the code, you can also change the location where the pivot table will be added. In the sample file, the TableDestination is set for the active sheet, in range A1.

pivot multiple sheets location

Then, after you make those small changes, click the button on the worksheet, and a summary pivot table is automatically created.

To download the sample file from Kirill and Héctor, click here: Pivot Table From Multiple Sheets.

Union Query from Data in Multiple Excel Files

Kirill Lapin shares his code to create a Union query and build a fully functional pivot table from data in multiple Excel files. For instructions, read the Contextures Pivot Table Blog article:

Macro Creates Excel Pivot Table From Multiple Files

There also an example file that creates either a pivot table or a formatted Excel table from the consolidated data. Download it from the Excel Sample Files page, in the Pivot Tables Section:

PT0033 - Pivot Table or Excel Table from Multiple Files

Download the Sample File

Download the sample pivot table tutorial file

Custom Search












Last updated: April 12, 2016 9:29 AM