Cross-tab data is where you have row headings as well as column headings. Consolidating (combining) multiple blocks of such data is a laborious process. Usually the rows and columns do not match across data blocks. You need to do a lot of copy-paste to bring them in the correct order before you can add a formula. Using a 20 year old unknown but powerful feature of Excel you can Consolidate Cross-Tab Data in minutes. You just select the data blocks and let Excel do the dirty work of matching row and column names.
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.
This is a sneak preview of my upcoming Udemy course on Pivot Tables. This video explains the relationship between Pivot Tables and File Size. Watch this 6 minutes video and learn from it.
Apply this knowledge to your existing pivots – especially those with large file sizes and see the difference. Make sure you try it out on a copy. Never learn using Live files.
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.
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.
Photo credit: Johnson Cameraface / Foter / CC BY-NC-SA
Suppose you have Products in Filter area and Pivot Table shows Regional Sales by Month. There are 5 products. You want five sheets for each product separately. But not by doing this manually. Here is how you do it (Reading time 7 min)