HOW TO: Create PivotTables from Multiple Consolidation Ranges in Excel 2000

In Excel 2000, 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 sample PivotTables that are created from multiple consolidation ranges. Additionally, the article provides examples of how to create associated charts that plot the data in the PivotTables.

Create Sample Data

To create the sample data for the sample PivotTables, follow these steps:

1. Start Excel, and then create a new workbook.
2. In Sheet1, enter the following data:

      A1: Eastern 2000  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 data:

      A1: Western 2000  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 data:

      A1: Eastern 2001  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. On the Insert menu, click Worksheet.
6. In Sheet4, enter the following data:

      A1: Western 2001  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 this sample data, you might want to create a page field for the 2000 data, and another page field for the 2001 data.

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

PivotTable Wizard to Create a Single Page Field

To create the PivotTable, follow these steps:

1. On the Insert menu, click Worksheet.
2. Click the Sheet5 tab to make Sheet5 the active worksheet in the workbook.
3. On the Data menu, click PivotTable and PivotChart Report.
4. In step 1 of the wizard, click Multiple consolidation ranges, and then click Next.
5. In step 2a of the wizard, click Create a single page field for me, and then click Next.
   
   NOTE: In steps 6 through 9 of this procedure, avoid selecting the data in row 1 from each of the ranges of sample data, because it is not used in the PivotTable.
6. In step 2b of the wizard, switch to Sheet1, select cells A2:E7, and then click Add.
   
   By doing this, you add the first range of data to the list of ranges for use in the PivotTable. In steps 7 through 9 of this procedure, you add the remaining ranges.
7. Switch to Sheet2, select cells A2:E6, and then click Add.
8. Switch to Sheet3, select cells A2:E7, and then click Add.
9. Switch to Sheet4, select cells A2:E8, click Add, and then click Next.
10. In step 3 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 which has four columns (one column for each quarter).

You can access the page fields for this PivotTable by clicking the 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 arrow in this example, you should see four additional items in the list (Item1, Item2, Item3, and Item4). If you click Item1, the PivotTable will display all of the data for the range containing the "Eastern 2000" 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. On the Insert menu, click Worksheet, and then make Sheet6 the active worksheet in the workbook.
2. On the Data menu, click PivotTable and PivotChart Report.
3. In step 1 of the wizard, click Multiple consolidation ranges, and then click Next.
4. In step 2a of the wizard, click I will create the page fields, and then click Next.
   
   NOTE: In steps 5 through 8 of this procedure, avoid selecting the data in row 1 from the ranges of sample data, because it is not used in the PivotTable.
5. In step 2b of the wizard, switch to Sheet1, select cells A2:E7, and then click Add.
   
   By doing this, you add the first range of data to the list of ranges to be used for the PivotTable. In steps 6 through 8 of this procedure, you add the other ranges.
6. Switch to Sheet2, select cells A2:E6, and then click Add.
7. Switch to Sheet3, select cells A2:E7, and then click Add.
8. Switch to Sheet4, select cells A2:E8, and then click Add.
9. Next to How many page fields do you want?, click 2 to set the number of page fields that you want to create.
   
   The Field one and Field two boxes become available.
10. In the All ranges box, click Sheet1!$A$2:$E$7, and then in the Field one box, type 2000.
11. In the All ranges box, click Sheet2!$A$2:$E$6, and then in the Field one box, click 2000.
12. In the All ranges box, click Sheet3!$A$2:$E$7, and then in the Field one box, type 2001.
13. In the All ranges box, click Sheet4!$A$2:$E$8, and then in the Field one box, click 2001.
14. Click Next.
15. In step 3 of the wizard, click Finish.

You 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). However, because you created two page fields, you should have a drop-down arrow for Page1 and a drop-down arrow for Page2. If you click the arrow for Page1, click 2000, and then click OK, only the data from Sheet1 and Sheet2 are summarized in the PivotTable. This occurs because you set up the first page field for the ranges from Sheet1 and Sheet2 and you named it 2000.

Create Charts from the Sample PivotTables

Create Chart Sheet from PivotTable

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

1. Switch to Sheet5.
2. Click the arrow for the Page1 page field (you should only have one for this PivotTable), click All, and then click OK.
3. Select cells A3:F15 (the range of cells for the PivotTable on Sheet5 with the Page Field set to All), and then press F11.

Create Column Chart from Chart Sheet

This procedure creates a column chart (assuming that you are using the default chart format) of all the data on a new chart sheet called Chart1.

To view only the data from one year, follow these steps:

1. Switch to Sheet5.
2. Click the arrow for the Page1 page field, click Item 1, and then click OK.
   
   The data for just Sheet1 appears in the PivotTable.
3. Switch to the Chart1 sheet.
   
   The chart has been updated to reflect only the data that is now displayed in the PivotTable on Sheet5.

Create Column Chart from PivotTable

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

1. Switch to Sheet6.
2. Click the arrow for the Page1 page field, click All, and then click OK.
3. Click the arrow for the Page2 page field, click All, and then click OK.
4. Select cells A4:F16 (the range of cells for the PivotTable on Sheet6 with both page fields set to All), and then press F11.

This procedure creates a column chart (assuming that you are using the default chart format) on a new chart sheet called Chart2. You can modify the data displayed in this chart in the same manner that you did in the previous sample chart.

For more information about PivotTables, click Microsoft Excel Help on the Help menu, type create a pivottable report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.