Use Excel pivot table as data source for another Pivot Table

Question

I have a Pivot table in excel that is using a raw table as its data source   This pivot table is doing a bunch of grouping and summing of rows     I d like to now use the result of this new pivot table as the data source for a new pivot table which will further modify this data     Is this possible with excel   I suppose you could call it  nested pivot tables

User · Answer

You have to convert the pivot to values first before you can do that    Remove the subtotals Repeat the row items Copy   Paste values Insert a new pivot table

User · Answer

In a new sheet  where you want to create a new pivot table  press the key combination  Alt D P   In the list of data source options choose  Microsoft Excel list of database   Click Next and select the pivot table that you want to use as a source  select starting with the actual headers of the fields   I assume that this range is rather static and if you refresh the source pivot and it changes it s size you would have to re-size the range as well  Hope this helps

User · Answer

Personally  I got around this in a slightly different way - I had a pivot table querying an SQL server source and I was using the timeline slicer to restrict the results to a date range - I then wanted to summarise the pivot results in another table   I selected the  source  pivot table and created a named range called  SourcePivotData    Create your summary pivot tables using the named range as a source   In the worksheet events for the source pivot table  I put the following code   Private Sub Worksheet PivotTableUpdate ByVal Target As PivotTable    Update the address of the named range ThisWorkbook Names  SourcePivotData   RefersTo         amp  Target TableRange1 Worksheet Name  amp        amp  Target TableRange1 AddressLocal   Refresh any pivot tables that use this as a source Dim pt As PivotTable Application DisplayAlerts   False For Each pt In Sheet2 PivotTables     pt PivotCache Refresh Next pt Application DisplayAlerts   True  End Sub   Works nicely for me

User · Answer

here is how I ve done this before    put a dummy column  X  off to the right of your source pivot table  click in that cell and start your pivot table   once the dialogue box pops up you can edit the data range to include your pivot table  this may require you to Refresh the source table  first and then refresh your secondary pivot table   or do refresh all twice

User · Answer

As suggested you can change the pivot table content and paste as values  But if you want to change the values dynamically the easiest way I found is Go To Insert- gt create pivot table Now in the dialog box in the input data field select the cells of your previous pivot table

User · Answer

I guess your end goal is to show Distinct  unique  values inside your original Pivot table   For example you could have data set with OrderNumber  OrderDate  OrderItem  orderQty  First pivot table will show you OrderDate and sum of OrderQty and you probably want to see Count of unique orders in the same Pivot  You woudln t be able to do so within standard pivot table   If you want to do it  you would need Office 2016  or perhaps pover Pivot might work    In office 2016 select your data   Insert   Pivot Table   choose tick  Add this  data to the Data Model   After that  you would be able to select grouping method as Distinct  Count

User · Answer

As  nutsch implies  Excel won t do what you need directly  so you have to copy your data from the pivot table to somewhere else first  Rather than using copy and then paste values  however  a better way for many purposes is to create some hidden columns or a whole hidden sheet that copies values using simple formulae  The copy-paste approach isn t very useful when the original pivot table gets refreshed   For instance  if Sheet1 contains the original pivot table  then    Create Sheet2 and put  Sheet1 A1 into Sheet2 A1 Copy that formula around as many cells in Sheet2 as required to match the size of the original pivot table  Assuming that the original pivot table could change size whenever it is refreshed  you could copy the formula in Sheet2 to cover the whole of the potential area the original pivot table could ever take  That will put lots of zeros in cells where the original cells are currently empty  but you could avoid that by using the formula  IF Sheet1 A1       Sheet1 A1  instead  Create your new pivot table based on a range within Sheet2  then hide Sheet2

User · Answer

Make your first pivot table   Select the first top left cell   Create a range name using offset  OFFSET Sheet1  A 3 0 0 COUNTA Sheet1  A  A -1 COUNTA Sheet1  3  3    Make your second pivot with your range name as source of data using F3    If you change number of rows or columns from your first pivot  your second pivot will be update after refreshing pivot GFGDT

[excel] Use Excel pivot table as data source for another Pivot Table

Examples related to excel

Examples related to pivot-table