MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table Defaults


April 10, 2017 - by Bill Jelen

Pivot Table Defaults

Hot Tip Alert! I waited seven years for this feature. If you love pivot tables, you will love this feature. Starting in 2017, you can change the format for all future pivot tables.

Watch Video

  • New in Office 365: Pivot Table Defaults!
  • All future pivot tables can be in tabular form.
  • Or, enable Classic Mode for all future pivot tables.
  • Go to File, Options. The feature keeps moving between a new Data category
  • or a Data category within Advanced.
  • The big settings in the dialog let you change the layout
  • But you can make small settings and Import those.
  • If you have an idea that you would like in Excel, go to Excel.UserVoice.com and write up your idea
  • It works!

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2072 set defaults for all future pivot
  • tables subtitle user voice works hey
  • welcome back to MrExcel NetCast I'm
  • Bill Jelen well here we are April 2017
  • well as 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 user voice excel user voice
  • calm this particular thing and vote and
  • boy two people vote we got over a
  • thousand both 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 is started having an Excel 2007
  • 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
  • right 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 might insider FAFSA
  • 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 the classic
  • release it might be a couple of months
  • are you going to file and Excel options
  • and here is the weird weird thing it's
  • going to be in one of two places is
  • 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 and
  • data where is it going to end up I don't
  • know I've asked exalting worth going to
  • end up with there 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
  • a which type of layout tabular outline
  • or compact absolutely whatever you like
  • I know you like to have you there right
  • everyone likes tab you choose that
  • there's my check box for repeat all item
  • labels boy I wish this one this one's
  • great out for regular pivot tables they
  • tease me by letting me think that I'm
  • gonna be able to turn that on but now 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 I'm not sure
  • I like that I was just testing that or
  • even if you would like the pivot table
  • classic right 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 run into
  • that love this feature I 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 cell 0 all of your
  • favorites can be there this is an
  • awesome awesome improvement to excel and
  • my 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 ash
  • on the Excel team said well hey you need
  • to go out to excel by user voice calm
  • 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
  • all right so recap new office 365 pivot
  • table defaults all future pivot tables
  • Konami in tabular form instead of
  • compact form or maybe you want to enable
  • classic mode for all future pivot tables
  • now to find it as one of two places go
  • to file options it's either in a brand
  • new data category or a data category
  • with in advance and I swear it keeps
  • moving back and forth so who knows where
  • it's going to end up the big settings
  • right in the dialog lets you choose the
  • layout repeat a light on labels tabular
  • form or you can make changes
  • ooh any pivot table and then import
  • those you know things like replace all
  • blanks with zeros never having the
  • column let's change and so on okay but
  • the subtext here is if you have
  • something that's driving you crazy and
  • Excel and you'd like the Excel team to
  • change it go to excel about uservoice
  • calm right up your idea get your
  • co-workers their 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

Download File

Download the sample file here: Podcast2072.xlsm

Title Photo: KlausHausmann / Pixabay


Bill Jelen is the author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.