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.