Multiple Data Consolidations for PivotTables

Question: As an effective tool for working with large volumes of data, I usually use PivotTables to summarize, organize and view the same data in many different ways quickly and easily. However the data is usually in one data source/range. Is it possible to create a PivotTable based on multiple data consolidation ranges?

Answer: Yes, with the PivotTable multiple consolidation ranges option.

Why:  To analyze data from multiple data consolidation ranges

Applies To:  Excel 2010, 2007, 2003, XP, 2000, 97 

1.  Enter the data given below in the three worksheets; sheet 1, sheet 2 and sheet 3 respectively.



















2.  Insert/Select sheet 4

3.  For Excel 2007 and 2010 press ALT + D and then press/type P

4.  For Excel 2003, XP, 2000 and 97; click the Data menu and then PivotTable & PivotChart Report

5.  The screen shot below will be displayed












6.  Select Multiple consolidation ranges and PivotTable then click Next

7.  Select create a single page field for me and then click next. The screen shot below will be displayed.










8.  Click on the red arrow under Range and select the data range A1:B9 on sheet 1

9.  Press Enter and click the add button

10.  Repeat steps 8 & 9 for data on sheet 2 and sheet 3

11.  Click on the next button and select Existing Worksheet then click Finish

As you can see a PivotTable with a multiple data consolidation range has been created. One can easily select the data to be displayed by selecting the appropriate option. The page option allows a user to select data for the respective worksheet.  The worksheets are given as item 1, Item 2 and Item 3.