Because of these two villains, millions of people waste their life struggling in Excel. Read on to find out how to handle them and eliminate them from your life.
Contents
Why are these bad?
Remember that we are working on Excel Tables. This is usually RAW DATA – means – input data. It contains lots of information – but not in a format which is easy to grasp. That is why we process it and generate some easier-to-understand output. That output usually contains Merged Cells and Cross-Tab data. Which is a good thing.
Unfortunately, very often, we get INPUT data which contains Merged Cells and Cross-Tab format. That is when they are bad – really bad!
Raw data is NOT supposed to have merged cells or cross-tab. It is just supposed to be simple tabular data.
That is why Excel does not allow you to create a table if the data contains merged cells.
Unfortunately, Excel has no way of understanding if the data is a cross-tab. Therefore, it allows you to create a table from a cross-tab.
Merged Cells and Cross-Tab primer
We know what is tabular data…
If we convert it to a Pivot Table to sales by product and region… it becomes a cross-tab
If we add the year as well, each region will have data for many years… so the Region cell is common across years… This is done automatically by Pivot table. This looks like a merged cell, but in reality it is not. In any case, the Region is common for multiple years – so conceptually it is a common (merged) cell.
Now you understand why these are OUTPUT formats.
What to do with merged cells?
If the INPUT DATA contains merged cells, it basically means that the data is a sort of output. It may not be created in a pivot table – but somehow, it is a report – not RAW data.
RAW DATA does not need merged cells.
So what to do? Just unmerge cells? That will not help…
The first data shows merged cells, the second one has all cells unmerged.
Now if you create a table, it will look like this…
This does not make any sense…
So we have to do something more. Usually, merged cells indicate multiple-level of headings. For example, in this case there is one set of headings for region and another one for years.
To handle this situation, we need to merge the two headings and create a single set of headings. Here is how it can be done.
This is better. The merge cell problem is now handled.
What we have now got is a CROSS-TAB. Let us see how to resolve this problem.
Converting Cross-Tab data to Tabular
In this article, we started with nice, tabular data. So we ALREADY KNOW how the raw data should be. But if you have got a cross-tabular data as shown above, how to convert it to tabular?
Let us understand the anatomy of the cross-tab data. The first column – Product – is good. It has headings followed by consistent data. But East 2014 and similar titles are actually DATA. In place of heading, data is sitting – which makes it BAD data.
To convert it to tabular, we have two methods.
NO. DON’T EVEN THINK OF MANUALLY DOING TRANSPOSE.
Read these articles to understand both these methods.
Consolidating crosstab data (this method works with all versions of Excel)
Crosstab data consolidation using Power Query (Excel 2010 Pro, with Power Query)
There is more
Consider our data. After using one of the above methods, you will finally convert it into tabular data like this.
The problem is still not fully solved. The Region-Year column must be split into two separate columns to get really good Tabular data. This can be done easily in Power Query itself.
What next
Now that we know the concepts well, we will see the rules for GOOD data and how to create good data from scratch – a skill which will help you throughout your life. More than the technical aspects, it helps you think more clearly and analyze data more effectively.
Related Articles
This article is a part of a series: Knowledge Pack – Excel Tables
***
