This is continuation of the previous article. In this article, we will see another way of creating Histograms using Pivot Tables.
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.
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.
I really don’t know how the world is still working. This is probably the most useful service I have done to humanity till date! It is dangerous mistake and is extremely common.
Read it NOW or view the 5 min video. Share it with everyone you love.
In this article, we will explore more benefits of using Power View in comparison with Pivot Table.
Estimated reading time 12 min
|Let us explore numeric grouping in Pivot Table with the Ageing Analysis scenario.Estimated reading time 7 min
Estimated time saving – hours!
Yes. This is a simple rule of efficiency. Of course, this rule is in the context of Office tools. But you never know, try applying it to real life – may be it is equally useful there. Using the example of GetPivotData – we will learn this concept.
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.
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.