Excel: Whatever Happened to Multiple Consolidation Range Pivot Tables?

Problem: I read your book Pivot Table Data Crunching, which describes an awesome trick for spinning poorly formatted data into transactional data for pivot tables. The trick requires you to choose Multiple Consolidation Ranges from Step 1 of the PivotTable and PivotChart Wizard. However, Microsoft seems to have eliminated the wizard in Excel 2007, so now how can I select Multiple Consolidation Ranges?

Strategy: Although the PivotTable and PivotChart Wizard has been removed from the ribbon, you can still get to the old wizard:

1. Type Alt+D followed by P.
2. The PivotTable and PivotChart Wizard will appear, complete with new artwork.

Additional Details: Using multiple consolidation ranges can help when your data is not properly formatted for pivot tables. Below, the data has been summarized with months going across the columns. Each year is on a different worksheet. All of the worksheets have products along column A, but the list of products differs from year to year.

1. Months going across rarely works for pivot tables.
   1. Type Alt+D+P to open the old PivotTable Wizard.
   2. In Step 1 of the wizard, choose Multiple Consolidation Ranges. Click Next.
   3. In Step 2a, choose I Will Create the Page Fields. (You don't have to create page fields, you just don't want Excel to create page fields.)
   4. Click Next.
   5. In Step 2b, choose the range on the first sheet. Click Add.
   6. Repeat Step 5 for each additional worksheet. The dialog should look like below.
   7. Click Finish.

   

2. Consolidate data from three worksheets.

   Excel will create a pivot table that summarizes all the worksheets. The fields have the strange names Row, Column, and Value.

   

3. The worksheets are combined into one pivot table.

   As you read in "œSee Detail Behind One Number in a Pivot Table," you can double-click any cell in a pivot table to drill down to see all the records in that cell. Here is the amazing trick: If you double-click the Grand Total cell in the pivot table, Excel will produce a new worksheet with all your data in detail format, as shown below. All you have to do is rename the headings from Row, Column, and Value to Product, Month, and Sales.

   

4. Double-click the Grand Total to get a data set combining data from all worksheets.