I hate the compact view for pivot tables. The PowerPivot team gives us Flattened pivot tables.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”!
PowerPivot for the Data Analyst chapter 7 - Compact Layout vs. Flattened!
Alright well, if you've ever seen me in any of my Excel seminars, you know that one of the things that I hate about all Pivot tables is this new thing they introduced in Excel 2007 called the Compact Layout.
Compact Layout says that, here let’s just build a Pivot table, put revenue, and then down the side we want to see perhaps region, and then store name.
Alright, So Compact layout throws both of those fields, region and store name, into a single column.
That is 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 in 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 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.
Well 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!
Alright well, if you've ever seen me in any of my Excel seminars, you know that one of the things that I hate about all Pivot tables is this new thing they introduced in Excel 2007 called the Compact Layout.
Compact Layout says that, here let’s just build a Pivot table, put revenue, and then down the side we want to see perhaps region, and then store name.
Alright, So Compact layout throws both of those fields, region and store name, into a single column.
That is 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 in 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 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.
Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
