Tag Archives: Pivot Tables

Consolidate Cross-Tab Data in minutes

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.

Additional Resources : Consolidate Cross-Tab Data

I have already covered this topic as a step-by-step article.  Now I have created a video (9 min). You can download the sample file used in the video and try it yourself.

Continue reading Consolidate Cross-Tab Data in minutes

Pivot Tables and File Size

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.

Continue reading Pivot Tables and File Size

Data Audit using Pivot tables

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

Macro to Explode Pivot table by Filter Field Items

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.

PivotFilterExplode macro

Photo credit: Johnson Cameraface / Foter / CC BY-NC-SA

Continue reading Macro to Explode Pivot table by Filter Field Items

Unknown Gems: Explode Pivot Table by each filter item

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- this is called Explode Pivot Table. But not by doing this manually. Here is how you do it (Reading time 7 min)

image

Continue reading Unknown Gems: Explode Pivot Table by each filter item