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.

Did you know? Pivot Tables can increase the file size!

Today I was conducting an efficiency optimization session for Risk Management team of a global bank. During the discussion, one of the problems we discussed was large Excel files. Although there are many reasons for large Excel files, one of the reasons is easier to manage and it is largely unknown. It relates to files containing one or more Pivot Tables.

Quick Summary

Pivot Table creates a copy of the source data and saves it in the file. This increases the file size and also slows down the open / close operations. You can ask Excel NOT to save the copy of the data and save on file size. Right click inside pivot, Pivot Table options, Data tab, clear the checkbox Save source data with file. That solves the file size inflation problem.

Read more to understand when to use and when NOT to use this feature.


Continue reading