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'
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.
Make your first pivot table.
Select the first top left cell.
Create a range name using offset:
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
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)
here is how I've done this before.
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 = "='" & 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 nicely for me! :)
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
Insert->create pivot table
Now in the dialog box in the input data field select the cells of your previous pivot table.
You have to convert the pivot to values first before you can do that:
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: