Pivot Table Defaults

Learn Excel from MrExcel Podcast, Episode 2072: Set Defaults for All Future Pivot Tables, Subtitle: User Voice Works

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  Well, here we are, April 2017.  Well, it’s one year ago, Podcast 1959 in November of 2015 that I put up this video, ranting about how I didn't like the compact form of Pivot Tables and why can't we have a way to make all future Pivot Tables show up in tabular form.  At that time, I actually bribed you to go out to this uservoice- excel.uservoice.com, this particular thing and vote.  And boy, the people vote.  We got over a thousand votes, and guess what?  Here we are, just 18 months later, and we now have a solution.

So here's the problem: Insert, Pivot Table, OK.  Put two items down the left-hand side, so, Customer and Region and Product.  By default,  those were showing up all in the same column, right?  And this has started to happen in Excel 2007 and it's been happening forever.  Very annoying, I wanted all that data to be in three columns like this.  And, oh by the way, I wanted the blanks to be filled in, alright.  This is what I wanted and thanks to you for voting and thanks to the Excel team for listening, we now have this awesome thing right now.  This is Office 365 only.  I’m on Insider Fast right here in April.  I have this, if you're an Insider Slow, it’ll be there in a few weeks.  If you're on the Classic Release, it might be a couple of months.

But you’re to File and Excel Options, and here is the weird, weird thing.  It's going to be in one of two places.  It’s either on the Data tab and it hangs out here on the Data tab a lot, until Excel crashes then it moves from the Data tab to the Advanced tab, all the way down in Data.  Where is it going to end up?  I don't know.  And I've asked the Excel team, where’s it going to end up with their being kind of coy about the whole thing.  So look in both places but here are the beautiful things: Edit Default Layout- BAM!  Right there, the things I asked for.  We can say which type of Layout: Tabular, Outline or Compact.  Absolutely, whatever you like and I know you like Tabular, right?  Everyone likes Tabular.  Choose that, there's my check box for Repeat all item labels.  Boy, I wish this one, this one's grayed out for regular Pivot Tables.  They tease me by letting me think that I'm going to be able to turn that on, but no, it doesn't work.  And even better than that, let's say that you have some sort of settings that you like, right?  So here, I've applied a green format and I'm not sure I like that.  I was just testing it out.  Or even if you would like the Pivot Table Classic, alright, so we're going to: Excel Options, Pivot Table Options.  And on the Display tab: Classic Pivot Table layout and that enables dragging a field on the grid.  You won't believe how much I run into that.  Love this feature.  Oh, hey, here's the cool thing.  So now we're in Classic layout, if I would go into: File, Options, Data or the Data Section of Advanced, Edit Default Layout, and say Import all settings from this Pivot Table, even replace empty cells with 0.  All of your favorites can be there.  This is an awesome, awesome improvement to Excel. 

Am I biased when I say it's awesome?  Yes, I'm biased because it was a feature that I've been asking for and frankly, I'm asking for it for five years until I asked on the Excel team.  I said, “Well, hey, you need to go out to Excel by uservoice.com and get people to vote for your idea.”  And I did, and it worked and it did my idea even better than my idea.  So, kudos to them and thanks to you for voting.  Check this out.  A great, great feature in Excel.

Alright, so recap: New Office 365 Pivot Table Defaults.  All future pivot tables can now be in tabular form instead of compact form, or maybe you want to enable Classic Mode for all future pivot tables.  How to find it, it’s one on two places:  Go to File, Options.  It's either in a brand new Data category or a Data category within Advanced.  And I swear, it keeps moving back and forth.  So who knows where it's going to end up.  The big settings is right in the dialog, let you choose the layout.  Repeat all item labels, Tabular form or you can make changes to any pivot table and then Import those, you know, things like Replace all blanks with zeros, Never having the column exchange, and so on.  Okay, but the subtext here is, if you have something that's driving you crazy in Excel and you'd like the Excel team to change it, go to excel.uservoice.com, right up your idea, get your co-workers to vote, get some momentum behind that, get a lot of votes and 18 months from now, your favorite feature request might just be in Excel.

Thanks for stopping by, we'll see you next time for another netcast from MrExcel.