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.