PivotTable from a PivotTable

While it is not technically possible to pivot a pivot table, you can reduce the number of records to analyze by using some of the functionality available in Excel 2010 and later versions.

Download Pivot Table from Pivot Table.xlsx to work along.

The Steps

1. To accomplish the goal of reducing the amount of data to analyze in a Pivot Table, we can pivot our starting source data and add each desired field to Row Fields. In our example, we’d only like to take the columns through Sum of Warehouse 4.
2. Things may look a little odd, right now, but in the next few steps this will all make sense. First let’s change the Design of our Pivot Table to Tabular Form. On the PivotTable Tools Design contextual tab, click the Report Layout button in the Layout group. Choose Tabular Form.
3. Next, remove all Subtotals and Grand Totals by clicking on the Subtotals and Grand Totals combo buttons respectively to turn them off.
4. Next, back on the Report Layout button, choose Repeat All Item Labels. This will give your worksheet a similar appearance as the original data source, minus the columns you didn’t want.
5. Click in the middle of your Pivot Table and press Ctrl+A to select all data. Filter out any rows you don’t want, as well. In our case, we’ll take only the first 10 products.
6. Perform a Paste Values operations into a new worksheet or workbook.
7. Pivot this new dataset, which contains about ½ of the original data.

So, while we did not actually pivot a Pivot Table, we used the Pivot Table functionality to reduce the volume of source data to the amount we really wanted, performed a copy paste values, and pivoted that!

Note: It’s always a good idea to document the steps you followed. Any automatic update or refresh capability you might have had with the original data source is not broken. You may need to repeat these steps on updated data to ensure that your analysis maintains data integrity.