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

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'

This question is related to excel pivot-table

The answer is

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.

  1. put a dummy column "X" off to the right of your source pivot table.
  2. click in that cell and start your pivot table.
  3. once the dialogue box pops up you can edit the data range to include your pivot table.
  4. this may require you to Refresh the source table first and then refresh your secondary pivot table...or do refresh all twice

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
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 Go To 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:

  • Remove the subtotals
  • Repeat the row items
  • Copy / Paste values
  • Insert a new pivot table

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.

Questions with excel tag:

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel? Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number EPPlus - Read Excel Table How to label scatterplot points by name? What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA? Multiple conditions in an IF statement in Excel VBA How to find and replace with regex in excel Unprotect workbook without password Excel is not updating cells, options > formula > workbook calculation set to automatic Find row number of matching value If "0" then leave the cell blank Clear contents and formatting of an Excel cell with a single command Remove Duplicates from range of cells in excel vba Delete worksheet in Excel using VBA Get list of Excel files in a folder using VBA Excel doesn't update value unless I hit Enter Declare a variable as Decimal Parse XLSX with Node and create json Detect if a Form Control option button is selected in VBA Get length of array? Object of class stdClass could not be converted to string - laravel Java - Writing strings to a CSV file Quickest way to clear all sheet contents VBA VBA: Counting rows in a table (list object) Excel VBA If cell.Value =... then VBA Excel - Insert row below with same format including borders and frames excel - if cell is not blank, then do IF statement filter out multiple criteria using excel vba Referencing value in a closed Excel workbook using INDIRECT? Use Excel VBA to click on a button in Internet Explorer, when the button has no "name" associated IndexError: too many indices for array File name without extension name VBA (Excel) Conditional Formatting based on Adjacent Cell Value Easy way to export multiple data.frame to multiple Excel worksheets Using ExcelDataReader to read Excel data starting from a particular cell What are the RGB codes for the Conditional Formatting 'Styles' in Excel?

Questions with pivot-table tag:

Use Excel pivot table as data source for another Pivot Table PivotTable's Report Filter using "greater than" How to SUM parts of a column which have same text value in different column in the same row Use formula in custom calculated field in Pivot Table refresh both the External data source and pivot tables together within a time schedule Convert Rows to columns using 'Pivot' in SQL Server Ordering issue with date values when creating pivot tables Python Pandas : pivot table with aggfunc = count unique distinct PivotTable to show values, not sum of values Simple Pivot Table to Count Unique Values Filter Excel pivot table using VBA MySQL - Rows to Columns How to change pivot table data source in Excel?