1. Why can't we do a Pivot Table Template?
2. Why can't we turn off the annoying Compact Layout?
3. Why do I keep asking these questions when it is so easy to override them with a tiny add-in?
The MrExcel Labs is coming out with a tiny add-in to solve Pivot Table Annoyances. This is your chance to weigh in with what 'Pivot Things' annoy you most and you can win a free copy of the add-in. Watch the podcast [Episode #1461] and find out all of the details!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
2. Why can't we turn off the annoying Compact Layout?
3. Why do I keep asking these questions when it is so easy to override them with a tiny add-in?
The MrExcel Labs is coming out with a tiny add-in to solve Pivot Table Annoyances. This is your chance to weigh in with what 'Pivot Things' annoy you most and you can win a free copy of the add-in. Watch the podcast [Episode #1461] and find out all of the details!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by MrExcel Labs and Makin’ Excel Easier.
Learn Excel from MrExcel podcast, episode 1461: solving pivot table defaults.
Hey, everyone.
Last week, I mentioned I was up at Michigan doing a seminar there and again, I get this question I get all the time.
Why can't you have a pivot table template?
You have chart templates; why not pivot table templates?
This time, you know, one guy asked, but the room was adamant.
There were like six or seven people saying, “Yes, yes, we need that.” You know, I've asked Microsoft this and I've asked them for the last couple of versions.
I know it takes about eight years for the things I asked for to happen.
I finally said, wait a second.
What am I waiting all this time for?
This is a few lines of code.
So, we're introducing something new here called the MrExcel Labs.
These are just tiny, tiny little programs.
They sell for nothing and try to make life easier.
So, my goal here, I want to come up with an add-in for-- I don’t know, what?
Three, four, or five bucks.
The whole thing is to solve pivot table annoyances and so I'm asking you, what drives you crazy about pivot tables?
When you create a pivot table, I hate compact view, right?
I understand Microsoft loves compact view.
It's horrible.
I don't want a compact view.
What annoys you?
Send me a note bill@mrexcel.com.
Hey, this is simple.
Send me your idea.
When the add-in’s out, I'll give you a free copy of the add-in.
All right, so, here's what I already have.
First of all, pivot table is going to be created with tabular view, not compact layout.
I hate those two, three, four, five columns all in column-- fields all in column A. Repeat, repeat all item labels; certainly, if you're in Excel 2010.
That was-- that was my idea put in it.
Empty cells should show zero instead of blank.
Who cares about the blank cells?
I always change them back to zero.
The one I hear, and this is controversial, do we want to let it resize the columns or not?
How about carrying the formatting through from the original fields?
Now, that's controversial and I'll show you why.
Then, do we apply any formatting?
Do we just choose some random format to use?
So, here's what I have so far in and literally this was in an hour this morning.
I have this little-- so here's the dataset.
I want to create a pivot table from this dataset.
Let's just do it the old way.
First, insert pivot table, OK and I’m going to choose region, product, revenue, cost of goods sold, but customers going across.
All right, so, some frustrations.
All of these fields here are in column A.
I hate that.
I always have to go to design and change that back to tabular form and then fill in the item labels, these blanks here.
That's options, options, for empty cells show zero, click OK.
The numeric formatting is always, always wrong so we have to go in and apply commas there.
Also, they don't choose-- they just choose some boring pivot table format.
I could change this.
I could make something else be my default.
So, maybe that's not a good one to include in the add-in.
Here, let me show you what I have so far.
So, here's my pivot table.
Instead of doing insert pivot table OK, I'm just going to come here to Less Stupid Pivot Defaults.
We might come up with a better name before it comes out.
So, you press that and here's what we get, okay?
So, we get a pivot table that has tabular turned on instead of compact.
Let's just do region and product.
All right, so, it automatically puts those in two columns.
Now, you'll notice people say, “I hate when it resizes the columns.” Well, wait a second.
I think I like when it resizes the columns because for me, it didn't resize those columns there and now I have to go back through and double-click it.
Then, the one thing I wanted to do that I found it was actually hard to do is I wanted to copy the formatting from the original source column.
So, if we go back and look at my data-- I actually went through here, that's numeric, zero decimal places, comma separator.
That had currency, currency and then that's the comma K format to show things in thousands.
I wanted to make that formatting stick, but I can't make it stick and then remove the field from the pivot table.
So, the way that this ends up getting created is all of the numeric fields are going to stay in the pivot table and then it's up to you to take the ones that you don't want out.
Is that going to annoy you?
I'm not sure, but at least now I don't have to go in and change the formatting.
If it's one where I was going to include three of the four fields anyway, well, then I think I actually like it.
Now, see it-- as soon as I remove them and put them back, I lost that formatting.
So, I'm going back and forth on this one.
Do we put the formatting in or not?
It's certainly a faster way to get to a nice-looking pivot table with the correct formatting, without that insane compact layout.
You know, that alone, I would-- I would pay three bucks to turn that compact layout off permanently.
So, I like that.
Okay, so let's just-- remember you’re going to shoot me a note: bill@mrexcel.com.
What else annoys you?
What other options should I turn on?
Then, kind of just check in on these.
Should we turn the resize columns off automatically?
Should we apply some formatting or let you set the defaults?
Should we put all those numeric fields in or not?
Let me know what you think.
Anyone who weighs in when this comes out, we'll get you a copy.
I guess-- if you send me a note, let me know the version of Excel: 2003, 2007, 2010, or Mac.
I was surprised there are people using Macs that watch this.
Obviously, it's going to be in VBA.
So, if you had that version of Mac-- was it 2008 without VBA, we’ll figure out what to do there.
All right, so that's the next quick project from MrExcel Labs.
I think it will be a good one.
I spend so much time complaining about all of this.
A few lines of code can just make it all go away.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1461: solving pivot table defaults.
Hey, everyone.
Last week, I mentioned I was up at Michigan doing a seminar there and again, I get this question I get all the time.
Why can't you have a pivot table template?
You have chart templates; why not pivot table templates?
This time, you know, one guy asked, but the room was adamant.
There were like six or seven people saying, “Yes, yes, we need that.” You know, I've asked Microsoft this and I've asked them for the last couple of versions.
I know it takes about eight years for the things I asked for to happen.
I finally said, wait a second.
What am I waiting all this time for?
This is a few lines of code.
So, we're introducing something new here called the MrExcel Labs.
These are just tiny, tiny little programs.
They sell for nothing and try to make life easier.
So, my goal here, I want to come up with an add-in for-- I don’t know, what?
Three, four, or five bucks.
The whole thing is to solve pivot table annoyances and so I'm asking you, what drives you crazy about pivot tables?
When you create a pivot table, I hate compact view, right?
I understand Microsoft loves compact view.
It's horrible.
I don't want a compact view.
What annoys you?
Send me a note bill@mrexcel.com.
Hey, this is simple.
Send me your idea.
When the add-in’s out, I'll give you a free copy of the add-in.
All right, so, here's what I already have.
First of all, pivot table is going to be created with tabular view, not compact layout.
I hate those two, three, four, five columns all in column-- fields all in column A. Repeat, repeat all item labels; certainly, if you're in Excel 2010.
That was-- that was my idea put in it.
Empty cells should show zero instead of blank.
Who cares about the blank cells?
I always change them back to zero.
The one I hear, and this is controversial, do we want to let it resize the columns or not?
How about carrying the formatting through from the original fields?
Now, that's controversial and I'll show you why.
Then, do we apply any formatting?
Do we just choose some random format to use?
So, here's what I have so far in and literally this was in an hour this morning.
I have this little-- so here's the dataset.
I want to create a pivot table from this dataset.
Let's just do it the old way.
First, insert pivot table, OK and I’m going to choose region, product, revenue, cost of goods sold, but customers going across.
All right, so, some frustrations.
All of these fields here are in column A.
I hate that.
I always have to go to design and change that back to tabular form and then fill in the item labels, these blanks here.
That's options, options, for empty cells show zero, click OK.
The numeric formatting is always, always wrong so we have to go in and apply commas there.
Also, they don't choose-- they just choose some boring pivot table format.
I could change this.
I could make something else be my default.
So, maybe that's not a good one to include in the add-in.
Here, let me show you what I have so far.
So, here's my pivot table.
Instead of doing insert pivot table OK, I'm just going to come here to Less Stupid Pivot Defaults.
We might come up with a better name before it comes out.
So, you press that and here's what we get, okay?
So, we get a pivot table that has tabular turned on instead of compact.
Let's just do region and product.
All right, so, it automatically puts those in two columns.
Now, you'll notice people say, “I hate when it resizes the columns.” Well, wait a second.
I think I like when it resizes the columns because for me, it didn't resize those columns there and now I have to go back through and double-click it.
Then, the one thing I wanted to do that I found it was actually hard to do is I wanted to copy the formatting from the original source column.
So, if we go back and look at my data-- I actually went through here, that's numeric, zero decimal places, comma separator.
That had currency, currency and then that's the comma K format to show things in thousands.
I wanted to make that formatting stick, but I can't make it stick and then remove the field from the pivot table.
So, the way that this ends up getting created is all of the numeric fields are going to stay in the pivot table and then it's up to you to take the ones that you don't want out.
Is that going to annoy you?
I'm not sure, but at least now I don't have to go in and change the formatting.
If it's one where I was going to include three of the four fields anyway, well, then I think I actually like it.
Now, see it-- as soon as I remove them and put them back, I lost that formatting.
So, I'm going back and forth on this one.
Do we put the formatting in or not?
It's certainly a faster way to get to a nice-looking pivot table with the correct formatting, without that insane compact layout.
You know, that alone, I would-- I would pay three bucks to turn that compact layout off permanently.
So, I like that.
Okay, so let's just-- remember you’re going to shoot me a note: bill@mrexcel.com.
What else annoys you?
What other options should I turn on?
Then, kind of just check in on these.
Should we turn the resize columns off automatically?
Should we apply some formatting or let you set the defaults?
Should we put all those numeric fields in or not?
Let me know what you think.
Anyone who weighs in when this comes out, we'll get you a copy.
I guess-- if you send me a note, let me know the version of Excel: 2003, 2007, 2010, or Mac.
I was surprised there are people using Macs that watch this.
Obviously, it's going to be in VBA.
So, if you had that version of Mac-- was it 2008 without VBA, we’ll figure out what to do there.
All right, so that's the next quick project from MrExcel Labs.
I think it will be a good one.
I spend so much time complaining about all of this.
A few lines of code can just make it all go away.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
