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.
Like what you've read? ...Republish it and share great business tips!
Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...
We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.
The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.
" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/33535/how-to-pivot-a-pivot "