XL: How to Create PivotTables from Multiple Consolidation Ranges

ID: Q142589

The information in this article applies to:

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 a PivotTable from multiple consolidation ranges, you use page fields in the PivotTable to identify the ranges of data. By using page fields, you can group ranges of related data or you can have a page that shows a consolidation of all of the ranges. For example, with the sample data above, you might want to create a page field for the 1992 data, and another page field for the 1993 data.

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, or click PivotTable Report in

   Microsoft Excel 97 or 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 "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 should now have a PivotTable in Sheet5 that has as many rows as there are unique entries in the sample data (that is, one row for each sport) and that has four columns (one column for each quarter).

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 list 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 click PivotTable Report in
    Microsoft Excel 97 or 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.

You should now have a PivotTable in Sheet6 that has as many rows as there are unique entries in the sample data (that is, one row for each sport) and four columns (one column for each quarter). But, because you created two page fields, you should have a drop-down for Page1 and a drop-down for Page2. If you click the drop-down for Page1 and click 1992, just the data from Sheet1 and Sheet2 are summarized in the PivotTable. This is because you set up the first page field for the ranges from Sheet1 and Sheet2 and you named it 1992.

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 list 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.

This procedure creates a column chart (assuming that you are using the default chart format) on a new chart sheet called Chart1. If you switch to Sheet5, click the drop-down list for the Page1 page field and then click Item 1, the data for just Sheet1 will appear in the PivotTable. If you then switch to the Chart1 sheet, you should see that the chart has updated to reflect just the data that is now displayed in the PivotTable on Sheet5.

To create a new chart sheet from the PivotTable on Sheet6, follow these steps:

1. Switch to Sheet6.

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

3. Click the drop-down list 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 F11.

This procedure creates a column chart (assuming that you are using the default chart format) on a new chart sheet called Chart2. If you switch to Sheet6, click the drop-down list for the Page1 page field, and then click 1992, the data for Sheet1 (Eastern 1992) and Sheet2 (Western 1992) is consolidated in the PivotTable. If you then switch to the Chart2 sheet, notice that the chart has updated to reflect just the data that is now displayed in the PivotTable on Sheet6.

REFERENCES

Microsoft Excel 97

For more information about PivotTables, click Contents And Index on the Help menu, click the Index tab in Excel Help, type the following text

   pivottable

and then double-click the selected text to go to the "Create a PivotTable" topic. If you are unable to find the information you need, ask the Office Assistant.

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

Additional query words: 8.00 pivot table

Keywords          : kbtool kbualink97 kbdta xlpivot 
Version           : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: January 8, 1999