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.
Contents
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.
More than you expect
It is possible that you already know this feature. But even then watch the video. I have put additional stuff which you may not be aware of.
- How to specify custom names for each block of data
- How to specify multiple sets of names and why
- How to create good, tabular data from the bad, cross-tab data
- How to correct spelling mistakes in the output pivot table, without changing anything in the raw data
Alternative method
Although this method works fine, in the world of Power BI, there is another efficient method available. This uses Power Query.
Read this article for details.
Crosstab data consolidation using Power Query
In this case you have to add a custom column to specify the name for each block of data. Page Field option does not exist in Power Query.
However, the data consolidated using Power Query can be put directly into the Data Model and used with other tools like Power Pivot and Power View.
Try out both methods and choose the right one depending upon your analytical needs.
