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.
Consolidation using PivotTables appears not to be available in Excel 2011 for the MAC, yet it is available in its predecessor, Excel 2008. Why can’t Microsoft/Apple get their act together and make the MAC version of Excel be on a par with the Windows version?Â
The sample data seems to suggest that the layout of the data on the different sheets needs to be identical; however, I have used the consolidation feature of PivotTables to pull together multi-column multi-row ranges from different worksheets when the layouts are not the same. This is a powerful capability indeed.
Dennistaylor, multiple consolidation for pivot table is available for mac 2011. press COMMAND+ALT+P.
Beginner here. I am attempting to consolidate data, but the pivot table doesn’t populate the fields needed. I.e. fields with alpha data. I looked into the functions provided but no avail. Suggestions anyone?
For Excel 2007 and 2010 press ALT + D and then press/type P pivot wizared is not comming how to prepare pivot usining multiple sheets in excel 2007