Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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'

like image 435
Jonathan Avatar asked Dec 02 '13 16:12

Jonathan


People also ask

Can you create a pivot from a pivot table in Excel?

In Excel, you can pivot data in a PivotTable or PivotChart by changing the field layout of the data. By using the PivotTable Field List, you can add, rearrange, or remove fields to show data in a PivotTable or PivotChart exactly the way that you want.

Can you use an external data source to bring data into an Excel pivot table?

Click Insert > PivotTable. In the Create PivotTable dialog box, click From External Data Source. Click Choose Connection. On the Connections tab, in the Show box, keep All Connections selected, or pick the connection category that has the data source you want to connect to.


2 Answers

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.

like image 194
user3357094 Avatar answered Sep 18 '22 08:09

user3357094


  • 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

like image 21
GFGDT Avatar answered Sep 19 '22 08:09

GFGDT