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.
