Multiple Data Consolidations for Pivot Tables

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.

Sheet 1





Sheet 2






Sheet 3





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.

Red arrow







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.