PowerPivot - Compact vs Flattened: Podcast #1292

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 23, 2010.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top