How to Create PivotTables from Multiple Consolidation Ranges

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.x
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, you can create a PivotTable from multiple consolidation ranges. These ranges can be on the same worksheet, on separate worksheets, or even in separate workbooks. This article provides two examples of PivotTables created from multiple consolidation ranges. In addition, the article provides examples of how to create associated charts that plot the data in the PivotTables.

MORE INFORMATION

In the two examples provided below, please use the sample data provided in the following steps:

1. Open a new workbook.

2. In Sheet1, enter the following:

         A1: Eastern 1992  B1:       C1:       D1:       E1:
         A2:               B2: qtr1  C2: qtr2  D2: qtr3  E2: qtr4
         A3: GOLF          B3: 42    C3: 97    D3: 57    E3: 38
         A4: TENNIS        B4: 70    C4: 70    D4: 93    E4: 40
         A5: SWIMMING      B5: 99    C5: 90    D5: 42    E5: 51
         A6: POLO          B6: 27    C6: 61    D6: 36    E6: 79
         A7: FOOTBALL      B7: 96    C7: 59    D7: 51    E7: 71
   
   

3. In Sheet2, enter the following:

         A1: Western 1992  B1:       C1:       D1:       E1:
         A2:               B2: qtr1  C2: qtr2  D2: qtr3  E2: qtr4
         A3: GOLF          B3: 86    C3: 9     D3: 24    E3: 56
         A4: TENNIS        B4: 30    C4: 59    D4: 82    E4: 91
         A5: SQUASH        B5: 75    C5: 41    D5: 52    E5: 76
         A6: FOOTBALL      B6: 12    C6: 94    D6: 23    E6: 14
   
   

4. In Sheet3, enter the following:

         A1: Eastern 1993  B1:       C1:       D1:       E1:
         A2:               B2: qtr1  C2: qtr2  D2: qtr3  E2: qtr4
         A3: TENNIS        B3: 99    C3: 37    D3: 80    E3: 70
         A4: SAILING       B4: 14    C4: 90    D4: 73    E4: 41
         A5: BASEBALL      B5: 15    C5: 89    D5: 12    E5: 3
         A6: FOOTBALL      B6: 3     C6: 53    D6: 65    E6: 13
         A7: DANCING       B7: 27    C7: 81    D7: 48    E7: 66
   
   

5. In Sheet4, enter the following:

         A1: Western 1993  B1:       C1:       D1:       E1:
         A2:               B2: qtr1  C2: qtr2  D2: qtr3  E2: qtr4
         A3: TENNIS        B3: 7     C3: 28    D3: 63    E3: 28
         A4: FOOTBALL      B4: 1     C4: 46    D4: 19    E4: 66
         A5: SQUASH        B5: 49    C5: 38    D5: 23    E5: 66
         A6: BIKING        B6: 38    C6: 94    D6: 0     E6: 55
         A7: GOLF          B7: 98    C7: 11    D7: 37    E7: 0
         A8: SWIMMING      B8: 158   C8: 72    D8: 74    E8: 56
   
   

When you create the PivotTable from multiple consolidation ranges, in step 2a of the PivotTable Wizard, you have the option of letting the Wizard create a single page field, or you can create the page fields (up to 4) yourself. This article gives an example for each of these two options.

Let PivotTable Wizard Create a Single Page Field in the PivotTable

To create the PivotTable, follow these steps:

1. Make Sheet5 the active worksheet in the workbook.

2. On the Data menu, click PivotTable. In Microsoft Excel 98 Macintosh Edition, click PivotTable Report on the Data menu.

3. In the "PivotTable Wizard--Step 1 of 4" dialog box, click Multiple Consolidation Ranges, and then click Next.

4. In step 2a of 4, click "Create a single page field for me," and then click Next.

   NOTE: In steps 5 through 8, avoid selecting the data in row 1 from each of the ranges of sample data, as it is not used in the PivotTable.

5. In step 2b of 4, switch to Sheet1, select cells A2:E7, and click Add.

   In step 5 you added the first range of data to the list of ranges for use in the PivotTable. In steps 6 through 8, you add the other ranges.

6. Switch to Sheet2, select cells A2:E6, and click Add.

7. Switch to Sheet3, select cells A2:E7 and click Add.

8. Switch to Sheet4, select cells A2:E8, click Add, and then click Next.

9. In step 3 of 4, click Next. Then, in step 4 of 4 of the wizard, click Finish.

You can access the page fields for this PivotTable by clicking the drop- down arrow in the first row (specifically, in cell B1). By default, the first page shown is a consolidation of all of the ranges of data. If you click the drop-down in this example, you should see four additional items in the list (Item1, Item2, Item3, and Item4). If you click Item1, the PivotTable will simply display all of the data for the range containing the "Eastern 1992" data (that is, the data you have on Sheet1 in this example).

Create the Page Field in the PivotTable

To create the PivotTable, follow these steps:

1. Make Sheet6 the active worksheet in the workbook.

2. On the Data menu, click PivotTable (or PivotTable Report in Microsoft Excel 98 Macintosh Edition).

3. In the "PivotTable Wizard--Step 1 of 4" dialog box, click Multiple Consolidation Ranges, and then click Next.

4. In step 2a of 4, click "I will create the Page Fields," and then click Next.

   NOTE: In steps 5 through 8, avoid selecting the data in row 1 from the ranges of sample data, as you it is not used in the PivotTable.

5. In step 2b of 4, switch to Sheet1, select cells A2:E7, and then click Add.

   In step 5 you added the first range of data to the list of ranges to be used for the PivotTable. In steps 6 through 8, you add the other ranges.

6. Switch to Sheet2, select cells A2:E6, and click Add.

7. Switch to Sheet3, select cells A2:E7, and click Add.

8. Switch to Sheet4, select cells A2:E8, and click Add.

9. In the "Wizard Step 2b of 4" dialog box, click "2" (located about two-thirds of the way down) to set the number of page fields you want to create.

   The Field One and Field Two boxes become available.

10. In the All Ranges box, click the range from Sheet1. In the Field One box, type 1992.

11. In the All Ranges box, click the range from Sheet2. In the Field One box, click 1992 from the list.

12. In the All Ranges box, click the range from Sheet3. In the Field Two box, type 1993.

13. In the All Ranges box, click the range from Sheet4. In the Field Two box, click 1993 from the list. Click Next.

14. In step 3 of the wizard, click Next. In step 4 of the wizard, click Finish.

Creat Charts from the PivotTable Examples

The following steps create a new chart sheet from the PivotTable on Sheet5:

1. Switch to Sheet5.

2. Click the drop-down for the Page1 page field (you should only have one for this PivotTable), and click All.

3. Select the range A3:F15 (the range of cells for the PivotTable on Sheet5 with the Page Field set to All) and press F11.

To create a new chart sheet from the PivotTable on Sheet6, do the following steps:

1. Switch to Sheet6.

2. Click the drop-down for the Page1 page field, and click All.

3. Click the drop-down for the Page2 page field, and click All.

4. Select the range A4:F16 (the range of cells for the PivotTable on Sheet6 with both page fields set to All) and press the F11 function key.

REFERENCES

Microsoft Excel 7.0

For more information about PivotTables in Microsoft Excel, click Answer Wizard on the Help menu and type:

   tell me how to make a PivotTable

Microsoft Excel 5.0

For more information about Creating a PivotTable in Microsoft Excel, click the Search button in Help and type:

    Pivot