Business Automation and Software Blog

Excel Tips and Tricks: Multiple Data Consolidations for Pivot Tables

Posted by Robert Baran on Thu, Jun 21, 2012 @ 12:19 PM

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

Excel Tips

Sheet 2

Excel Tips

Sheet 3

Excel Tips

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.

Excel Tips

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.

Excel Tips

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.

Contact PositiveVision ► ► for a Complimentary  Reporting Consultation

Learn more about PositiveVision's business management and operational reporting services...

Topics: Business Reporting, Excel Tips and Tricks