Create PivotTable From Multiple Consolidated Ranges

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn the old way of creating PivotTable from multiple consolidated ranges using the Office 2003 PivotTable and PivotChart wizard. Let us get started

In the captions below, we have January: March Report of sales data showing Account Manager and the values for each Product which are Seafood, Beverages, Dairy Products and Condiments

Consolidate Ranges in Old PivotTable Wizard

The next thing we want to do is to create PivotTable using the Office 2003 old wizard which allows us to consolidate from multiple ranges

To do that:

  • Execute ALT + DP on the keyboard. This will open the old PivotTable and PivotChart Wizard

  • Click on Multiple consolidation ranges and click on Next

We want to create a single page field which is already checked. Therefore, click on Next

  • In the Step 2b of 3, we need to provide all the sheets to consolidate.
  • Click inside the Range and select all the data from January Report sheet using CTRL + SHIFT + Down and Left arrows. Note, ensure that all the headings are selected. See the caption below

  • Click on Add.
  • Repeat the last two steps for Feb Report and March Report.

In the caption below, we have all the ranges as seen in the caption below

  • Click on Next

The Step 3 of 3 asks us where we want to place the PivotTable. In this case, we want a New worksheet which is automatically checked

  • Click on Finish

As seen in the caption below, we have the PivotTable Report.

 

One caveat with this old PivotTable is that we only get to see Row, Columns, Value and Page 1. Even when we use Show in Tabular Form, it doesn’t change to the original name of the heading we have in the Source data.

Posted in Blog and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *