Very common problem. Very easy solution. Deserves a separate article. This alone can save hundreds of thousands of person years of drudgery globally every day.
This article shows the problem and the solution in a concise way. Quick and Easy.
Contents
The problem
Pivot Table is often used in Tabular mode (Left) because people do not like the Compact mode (Right).
The data is same. The analysis is same. But the tabular mode allows you to use output of one pivot table as an input to another pivot table if you copy and paste it as values.
Of course, there will be gaps in the data. Observe that after the business type Corporate and Retail there are few row empty. Now, you must fill these gaps before you can use the pasted data as another data source. Unless these gaps are filled, sort, filter or further analysis is impossible.
Here is a more complex example – with more columns and consequently – more gaps to fill.
The wrong way
Everyone who has experienced this situation knows that the job of filling these gaps is the job of the human being – and not Excel. Therefore, we end up spending inordinate amount of time trying to fill the gaps.
Depending upon your level of proficiency or the lack of it, there are at least 5 different methods available to fill these gaps manually. Some of these methods of deplorably inefficient and some are technically sound. But that is not the point.
The point is that we forget the fact that Microsoft is observing all our requirements and providing solutions for them continuously. Even after 25 years, their zeal for simplifying our lives has not diminished!
Remember: Struggle means ignorance. When you catch yourself struggling, assume that there must be a better way and find it.
The right way
While everyone thought that pivot table is not capable of filling these gaps, Microsoft added a small little solution to Pivot table. But hardly anyone has noticed it.
This works only in Tabular mode of Pivot Table.
If you want to be sure, first go to Pivot Table tab – Design – Report Layout and choose Tabular Layout
Now open the same dropdown and choose Repeat All Item Labels.
That’s it. All the gaps in Pivot Table are automatically filled.
This is what is called as a Flattened Pivot Table. This option is directly available from Power Pivot menu.
What if you don’t have the original pivot table?
If someone pasted the pivot as values and then sent it to you, this option is not going to work. In that case you need to fill the gaps manually. We will cover that methodology in the next article.
When do you use different report layouts?
Refer to the screen images shown earlier – Tabular and Compact layout.
Tabular layout uses one physical column for every field added. Due to this the width of the pivot table increases every time you add a field. This increases the scrolling requirements, making the pivot table less convenient to interpret. If you want to paste pivot as values, and use the output as another input (after filling the gaps), you must use this layout.
Therefore, the compact mode was created where the pivot table continues to occupy only 1 row even if you add multiple fields in the row area. This view is more convenient to interpret visually.
There is a third layout called Outline view. It is similar to tabular view but adds a separate row for every item in every field. Increases the number of rows occupied by the pivot table.
For onscreen reading and publishing to Excel Services (SharePoint / OneDrive for Business), the compact view is better. It also reduces the width – so suitable for printing as well.
Tabular view is useful if you want to copy paste the pivot as an input to some other process. It is also useful in cases where traditional reports have to be matched as per some long standing rules or norms. Most of the customization required to satisfy such requirements is not supported by Pivot table. That is another reason why copy paste as value is required often.
This feature is there since 2010. Did you notice it?
If you noticed and used it, good for you. But if you did not notice this feature before, it is also possible that many other useful features are being ignored by you.
So the moral of the story is : Explore. Don’t ignore!
— 
—
