49. Videotutorial – Deal with Pivot Tables 9 – Multiple consolidation ranges

Another episode of tutorials Deal with Pivot Tables continues with the topic how to consolidate multiple source table into one Pivot. It can be achieved using PivotTable and PivotChart wizard, which remained in Excel from older versions. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Please note that this tutorial is presented in czech language, with english subtitles.

Source file can be downloaded here.

In the PivotTable we need to learn a few more things, but still useful. One of them is the possibility to consolidate more tables. Ahead we see three tables, the number of births and deaths in different cities. The tables do not have the same size or order of the columns. In our process it does not matter, the summary will be under the same names.

To be able to consolidate them into one PivotTable, we need to create it activating a wizard, which remained from the older version. Therefore, on the Quick Access Toolbar add the icon, select File > Options > Quick Access Toolbar, and on the left we find in the category All commands here click PivotTable and PivotChart Wizard, add to the right side. This button appears on the toolbar to activate it.

This step-by-step wizard offers several steps, in the very first we choose Multiple consolidation ranges. In the next step, we do not change anything, in the following add one table after another to the list of merging areas. Finally, select the target cell, into which the PivotTable is placed.

In the created PivotTable we can use standard procedures illustrated in previous episodes such as totals, filtering, etc. In the Filters section, we find strangely named field that represents our three source tables, you can drag it to the Rows section and different “items” rename as cities. If we want to edit the source area whenever we can invoke the wizard again and step back to get to the selection tables, delete, or add a new and the PivotTable is then updated.

What can I say more? Do not forget to watch the final episode of this video series where we introduce tips and tricks that did not fit elsewhere.