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.