Pivot Table From Single or Multiple Pivot Table

Pivot of a Pivot ? Is it possible in Microsoft Excel ?

Yes, it's possible in Excel even you can create Pivot Table from multiple Pivot Tables.

In this article, we will discuss how to create Pivot Table from another Pivot Table as data source.

We will also discuss how to create Pivot Table from multiple Pivot Table as data sources.

To get PivotTable from another PivotTable as source, we need to use PivotTable and PivotTable Chart Wizard.

 PivotTable and PivotTable Chart Wizard

Microsoft Excel 2007/2010/2013/2016/2019 hides the PivotChart Wizard, which is not shown in the Ribbon.

Without Classic Menu for Excel installed, you will not find it. 

To use 'PivotTable and PivotChart Wizard' as menu item, there still are several ways to configure it.

Excel also provided us shortcut to get this wizard, Alt+D+P.

If you press these keys combined, a new window will appear, the PivotTable and PivotChart Wizard.

The problem with this shortcut is that every time you need PivotTable and PivotChart Wizard you must press these key combination.

The permanent solution is to get it somewhere in the menu. Therefore, lets first learn how to get PivotTable and PivotChart Wizard in the menu or as a button permanently.

Steps to configure the Wizard :

1.   Click File / Office Tab, and click “Options” item.

2.   Click the Customize Ribbon / Excel Options.

3.   In the Excel Options window, you will see Customize the Ribbon or Customize option.

4.   Choose Commands Not in the Ribbon or All Commands by clicking the arrow, and then use the scroll bar to search for the PivotTable and PivotChart Wizard.

5.   When finally find out the Wizard, click Add to add it into the Ribbon / Quick Action Toolbar, and then click OK to finish the step.

The images are shown Step by step.

Excel Option to Create PivotTable and PivotTable Chart Wizard

Excel Option to Create PivotTable and PivotTable Chart Wizard

Excel Option to Create PivotTable and PivotTable Chart Wizard

Now, as we have configured the PivotTable wizard successfully, we can use this wizard to create PivotTable from another PivotTable as data source.

Steps :

1.   Select any blank cell in the Sheet

2.   Use the button in the Quick Access Toolbar or alternatively you can use Alt+D+P shortcut to start the PivotTable and PivotChart Wizard.

Quick Access Toolbar Button - PivotTable and PivotTable Chart Wizard

PivotTable and PivotTable Chart Wizard Step 1

3.   You will notice that multiple options are visible and the top option 'Microsoft Office Excel list or Database' is selected.

4.   Click 'Next', it will ask you to select the range, select the range from the existing PivotTable and click 'Next'.

PivotTable and PivotTable Chart Wizard Step 2

5.   The third and last step is to select the location of the new PivotTable either in Existing Worksheet or in a New Worksheet.

PivotTable and PivotTable Chart Wizard Step 3

6.   Click 'OK' and you will get your PivotTable using previous PivotTable as data source for the new PivotTable.

Example :

I am using Excel 2007 and in Excel I have inserted a PivotTable source is from a data table in this Excel Workbook which simply shows Day wise Date wise Target of various products.

To understand it better I have given a simple image.

PivotTable from another PivotTable as Data Source

PivotTable from Multiple PivotTables

So you learnt how to create PivotTable from another PivotTable, the second question comes in mind, How to create  PivotTable from Multiple PivotTables ?

1.   It is very simple, notice the PivotTable and PivotChart Wizard's third option, "Multiple consolidation ranges", click the radio button to select this option and click 'Next'.

PivotTable from Multiple PivotTable

2.   The next step will ask you to select 'Create a single page field for me' and 'I will create the page fields', select the first option, 'Create a single page field for me', click 'Next'.

PivotTable from Multiple PivotTable

3.   Now, in this step Excel wants to take input "Where are the worksheet ranges that you want to consolidate?"

        a) Select the first range i.e., from first PivotTable and click 'Add' which will add the location of the PivotTable in the below box

        b) Again select the second PivotTable and click 'Add'.

PivotTable from Multiple PivotTable

4.   Now, choose the location where you want to insert your new combined PivotTable, click 'Finish'.

PivotTable from Multiple PivotTable

Now your data is ready.

Example of PivotTable from Multiple PivotTable

Note : A report filter is automatically generated in this PivotTable which if selected will display data from the specific locations.

Hope you have enjoyed reading this article ...

Keep reading, sharing & Stay blessed ...