Use an Excel pivot table as a data source for another pivot table

Question

I have a pivot table in excel that uses the source table as the data source. This pivot table groups and summarizes rows.

I would like to use the result of this new pivot table as a data source for a new pivot table that will further modify this data.

Is this possible with excel? I suppose you could call it "nested pivot tables"

Answer 1

On 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, select Microsoft Excel Database List. Click Next and select the PivotTable that you want to use as a source (select from the actual field headers). I assume that this range is quite static, and if you update the source point and resize it, you will have to resize the range. Hope this helps.

Answer 2

• Make your first pivot table.

• Select the first upper left cell.

• Create a range name using the offset:

  OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$3:$3))

• Take a second turn with the range name as the data source using F3.

If you change the number of rows or columns in your first pivot list, your second pivot will be updated after updating the pivot.

Gfgdt

Answer 3

Before you can do this, you first need to convert the support element to values:

• Delete subtotals
• Repeat Line Items
• Copy / Paste Values
• Insert new pivot table

Answer 4

As follows from @nutsch, Excel will not do what you need directly, so you need to first copy the data from the pivot table to another location. However, instead of using copy values ​​and then pasting values, the best way for many purposes is to create some hidden columns or an entire hidden sheet that copies the values ​​using simple formulas. The copy-paste method is not very useful when the original pivot table is updated.

For example, if Sheet1 contains the original pivot table, then:

• Create Sheet2 and put =Sheet1!A1 in Sheet2! A1
• Copy this formula around as many cells as possible in Sheet2 to fit the size of the original pivot table.
• Assuming that the original pivot table may change in size whenever it is updated, you can copy the formula to Sheet2 to cover all the potential area that the original pivot table could take. This will result in a lot of zeros in the cells where the source cells are currently empty, but you could avoid this by using the formula =IF(Sheet1!A1="","",Sheet1!A1) instead.
• Create a new range-based pivot table in Sheet2, then hide Sheet2.

Answer 5

Personally, I went around this a bit differently - I had a pivot table asking for the source of the SQL server, and I used a time slicer to limit the results to a date range. Then I wanted to summarize the summary results in another table.

I selected the "source" pivot table and created a named range called "SourcePivotData".

Create pivot pivot tables using the named range as the source.

In the worksheets for the original 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 = "='" & Target.TableRange1.Worksheet.Name & "'!" & 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 well for me! :)

Answer 6

this is how i did it before.

• put the dummy column "X" to the right of the original pivot table.
• click in this cell and run your pivot table.
• After the dialog box appears, you can edit the data range to include your pivot table.
• to do this, you may need to first update the source table and then update the secondary pivot table ... or update everything twice

Answer 7

I assume that your ultimate goal is to show the distinctive (unique) values ​​inside your original Pivot table.

For example, you might have a dataset with OrderNumber, OrderDate, OrderItem, orderQty

The first pivot table will show you OrderDate and the amount of OrderQty , and you probably want to see the number of unique orders in the same Pivot . You cannot do this in the standard pivot table

If you want to do this, you'll need Office 2016 (or maybe Pover Pivot might work). In office 2016, select your data> Insert> PivotTable> select the checkbox "Add this data to the data model"

After that, you can select the grouping method as Distinct (Count)

Answer 8

As suggested, you can modify the contents of the pivot table and insert as values. But if you want to change the values ​​dynamically, the easiest way I found is to go to Insert->create pivot table Now in the dialog box in the input field, select the cells of your previous pivot table.