For those with nice clean simple datasets to work with, this might seem like a silly thing to want to do. For those of us who deal with some monster-sized data exports, this could solve so many problems. While you can’t EXACTLY pivot a pivot, in Excel 2010 you can create a data subset with a Pivot Table that can be copied, pasted and “re-pivoted” in just a few clicks.
- Create a Pivot table from your large data set. Select all the fields you’d like to include in your subset. Don’t worry about where the fields land in the Layout Area.
- Now, from the Design tab, in the Layout group, click Report Layout and choose Show in Tabular Form.
- Drag any fields in the Values area of the Layout to Row Labels. Be sure the order of the fields is what you'd like to see in your data subset.
- Again, from the Design tab, in the Layout group, click Report Layout and choose Repeat All Item Labels.
- Filter any fields you wish to reduce the number of records to only what you’d like to see in your data subset.
- Finally, we’ll clean it up by removing all Subtotals and Grand Totals with the two buttons to the left of Report Layout.
What you should have left is a good representation of your new data subset. Now, just select all your data (try Ctrl+A), copy (Ctrl+C) and paste values to a new worksheet.