Excel -- Pivot Tables --
Multiple Consolidation Ranges

  1. Create a pivot table from multiple consolidation ranges
  2. Limitations of multiple consolidation ranges

Download the sample file

 

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.

 


Source data  

 

Create a pivot table from multiple consolidation ranges

  1. Choose Data | PivotTable and PivotChart Report
  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, click Finish
  2. In the Column dropdown, hide any columns that contain meaningless data.

 

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's 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 avoid this, you can rearrange your database columns, and then use data ranges that only include the columns that you want to total.

If possible, move your data to a single worksheet, or store it in a database, such as MS Access, and you'll have more flexibility in creating the pivot table.

Or, you can create named ranges in an Excel file, and use MS Query to combine the data. There are sample files here: http://www.contextures.com/excelfiles.html#PT0007

 


1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection  
 
  
13. Pivot Tables -- Grand Totals
 
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

 
Last updated: July 18, 2008 11:47 PM