Excel Tips and Tricks / Business Reporting 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 options.
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.
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.
If you would like additional assistance with your company's business reporting, please contact us today for a complimentary consultation.
Learn more about PositiveVision's business management and operational reporting services...