In Episode #1292, Bill tells us why he doesn't really care much for the Compact Layout of Pivot Table data and why it is limiting - and sometimes time consuming to work with. What to do? Use the 'Flattened Pivot Table' option in Excel 2010!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
PowerPivot For The Data Analyst: Chapter 7, Compact Layout vs. Flattened.
All right! Well, if you've ever seen me in any my Excel seminars you know, that one of the things that I hate about all Pivot Tables is this a new thing they introduced an Excel 2007, called the Compact Layout, Compact Layout says, that let's just build a Pivot Table, put Revenue and down the side we want to see perhaps Region and then store name.
All right! So, compact layout throws both of those fields region an and store name into a single column.
That is so, so wrong because frankly every Pivot Table that I create is not going to live as a Pivot Table.
I'm going to take this data, I'm going to copy it, I'm going to paste it as values and then need to use it and now they've jammed two different fields right here in the leftmost column.
I mean if we add a third field, it gets even worse because now we have three fields in that column.
and so for every single Pivot Table that I create, I instantly come back here to the Design tab, choose Report Layout, Show in Tabular Form, and then new and Excel 2010, I also say, Repeat All item Labels.
So, we do this every single time that I create a Pivot Table, and you say why did they choose compact layout as the default, is there a way to make tabular be the default.
No, no there's not, but again the PowerPivot people, I think seem to understand this problem.
I'm going to make my bet that it was Rob who spent some time on the Excel team and he understands, how bad that compact layout is.
So, this time I'm going to create a Pivot Table, but instead of just creating a pivot table.
I'm going to create a Flattened Pivot Table.
Flattened Pivot Tables are cool because they have a couple of settings by default.
First thing if we choose multiple fields, down the left hand side.
For example, Region and store name look they've already put it in tabular form and they've already choose, chosen to repeat all item labels all the way down.
So, the Flattened Pivot Table, just a really, really nice improvement solves that problem of you know, how do I get rid of the compact layout without having to choose it every time, this is one of those reasons.
Why I want to take all of my data even just simple little excel data sets and run it through PowerPivot because now, I don't have to do those settings anymore.
Oh! Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
PowerPivot For The Data Analyst: Chapter 7, Compact Layout vs. Flattened.
All right! Well, if you've ever seen me in any my Excel seminars you know, that one of the things that I hate about all Pivot Tables is this a new thing they introduced an Excel 2007, called the Compact Layout, Compact Layout says, that let's just build a Pivot Table, put Revenue and down the side we want to see perhaps Region and then store name.
All right! So, compact layout throws both of those fields region an and store name into a single column.
That is so, so wrong because frankly every Pivot Table that I create is not going to live as a Pivot Table.
I'm going to take this data, I'm going to copy it, I'm going to paste it as values and then need to use it and now they've jammed two different fields right here in the leftmost column.
I mean if we add a third field, it gets even worse because now we have three fields in that column.
and so for every single Pivot Table that I create, I instantly come back here to the Design tab, choose Report Layout, Show in Tabular Form, and then new and Excel 2010, I also say, Repeat All item Labels.
So, we do this every single time that I create a Pivot Table, and you say why did they choose compact layout as the default, is there a way to make tabular be the default.
No, no there's not, but again the PowerPivot people, I think seem to understand this problem.
I'm going to make my bet that it was Rob who spent some time on the Excel team and he understands, how bad that compact layout is.
So, this time I'm going to create a Pivot Table, but instead of just creating a pivot table.
I'm going to create a Flattened Pivot Table.
Flattened Pivot Tables are cool because they have a couple of settings by default.
First thing if we choose multiple fields, down the left hand side.
For example, Region and store name look they've already put it in tabular form and they've already choose, chosen to repeat all item labels all the way down.
So, the Flattened Pivot Table, just a really, really nice improvement solves that problem of you know, how do I get rid of the compact layout without having to choose it every time, this is one of those reasons.
Why I want to take all of my data even just simple little excel data sets and run it through PowerPivot because now, I don't have to do those settings anymore.
Oh! Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
