Search Contextures Sites

Pivot Table -- Multiple Consolidation Ranges

  1. Create a Pivot Table from Data on Different Sheets
  2. Create a pivot table from multiple consolidation ranges
  3. Video: Create a Pivot Table from Multiple Sheets
  4. Limitations of multiple consolidation ranges
  5. Alternatives to Multiple Consolidation Ranges
  6. Download the Sample File
  7. Pivot Table Training
  8. Excel Pivot Table Tutorial List

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.

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.

Create a pivot table from multiple consolidation ranges

  1. Choose Data | PivotTable and PivotChart Report (In Excel 2007, press Alt+D, then press P)
  2. Select Multiple consolidation ranges, click Next

  3. Select one of the page options, click Next
  4. Select each range, and click Add
  5. If you chose 'I will create the page fields', you can select each range, and assign field names, in step 2b
  6. Click Next

  1. Select a location for the PivotTable, then click Finish
  2. In the Column dropdown, hide any columns that contain meaningless data. For example, the Colour column might contain all zeros, because the colours are text, not numbers.

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.

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.

Alternatives to Multiple Consolidation Ranges

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

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  

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Pivot Table Training

If you're looking for a video-based pivot table course,I recommend the following. Both are excellent quality, with clear, easy-to-follow instructions.

Xtreme Pivot Table course

By John Michaloudis, at My Excel Online.

Videos: 200+, total time, approx 9 hours, download

Sample Excel workbooks, to use with the video lessons.

Summary: Detailed coverage of pivot table topics, with in-depth coverage of topics.

Core Pivot

By Dave Bruns at ExcelJet.

Videos: 49, total time, approx 2.5 hours, online

Sample Excel workbooks, to use with the video lessons.

Summary: Short, to the point, coverage of pivot table topics, for rapid learning



More Pivot Table Resources




More Tutorials:


Excel news
by email










Learn how to create Excel dashboards.


Privacy Policy


Contextures Inc., Copyright ©2014
All rights reserved.



Last updated: November 2, 2014 3:39 PM