What did I learn today: Pivot Table Transpose

Consider a pivot table which has many fields in row as well as column area. Now, for whatever reason, you have to transpose the pivot table. Whatever is in the rows has to go into columns and vice versa. We cannot use Paste Special Transpose with Pivots.

The only choice seems to be manually dragging and dropping fields across row and column areas. Not only is this cumbersome, but it can also lead to mistakes. Don’t worry. I just found a smarter way.

Add a Pivot Chart. Never mind which type. Choose Pie because it takes least amount of effort graphically and it happily ignores child series of data. Now click inside the chart. Choose Design tab and click Switch Rows / Column. It instantly transposes the row and column fields. Delete the chart. Job done.

image  image  image

This works with Power Pivots as well. For large pivot tables, you may get the maximum series limit reached error for charts. Ignore that error and continue – because in this case, the chart is just a temporary means of achieving transpose operation.

Pivot Filter Explode Macro

Refer to the previous post. We saw how Filter fields can be exploded into individual sheets and files. In this article, I am releasing a macro – which does all the hard work for you. Of course, you must use it at your own risk. The source code is also included for you to view, learn from and modify. Created by Raj Chaudhuri and myself.

image

Photo credit: Johnson Cameraface / Foter / CC BY-NC-SA

Continue reading