I am happy to announce a new, comprehensive course on Pivot Tables on Udemy. As a special benefit to blog readers, I am including a promo code for FREE access (available to first five readers, on a first-come-first-server basis). Read on for more details.
Continue reading Pivot Tables Second Honeymoon : New course on Udemy
I wrote an article about Data Audit using Pivot long back. Now, I have converted it to a comprehensive video. Have a look:
How to perform Data Audit using Pivot
Raw data can contain many columns and thousands of rows. Checking the consistency and accuracy of data cannot be done by simply scrolling and eyeballing the data. Too time-consuming.
Continue reading Data Audit 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.
Yes. This is possible. Just add Pivot Charts from the Analyze (or Options) menu of Pivot Table. Multiple charts can be added. Choose different visualization for each chart to get varied views of the same data. For example, column chart for comparison and 100% column chart for viewing the relative distribution.
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.
Continue reading Macro to Explode Pivot table by Filter Field Items