Peltier Tech

Peltier Technical Services - Excel Charts and Programming

User Voice Fixes Pivot Table Default Settings

by 21 Comments

Wednesday, May 31, 2017 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

I’ve been using Pivot Tables in Excel for twenty years or more. They are one of the features that have made Excel such a killer program. Pivot Tables are easy to use after a bit of learning, and they’re fast and flexible. And the Pivot Table default settings used to be alright.

A few Excel versions ago, I believe in Excel 2007, Microsoft added some additional layouts to Pivot Tables, so in addition to the historic Tabular layout, you can now choose Compact or Outline. The problem is that they made Compact the default, and except for the simplest Pivot Tables, I never want to use Compact, I always want the Tabular layout. So right after I build a Pivot Table, I need to fix it. (This may have been Yet Another Reason to hate Excel 2007.)

There are a few other default settings that I need to correct these as well, though they were never as annoying as the Compact layout.

Let me illustrate the problem. I have a simple Table with the headings “Greek” (containing “Alpha”, “Beta”, or “Gamma”), “Latin” (containing “A”, “B”, or “C”), “Arabic” (containing (1, 2, 3, or 4), and “Values” (containing a random two-decimal-digit number between 3 and 8). When I insert a Pivot Table with Greek in the columns area, Latin and Arabic in the rows area, and Values in the values area, here is the out-of-the-box default Pivot Table.

Pivot Table Built-In Pivot Table Default Settings

What’s wrong with this Pivot Table?

  1. It’s in compact layout, which means all of the row pivot fields are compressed into one column, with field items indented according to their order in the rows area. The field names are not shown at the top of the field; instead there is a single label called Row Labels.
  2. There are subtotals, and I usually do not want subtotals.
  3. There are grand totals on rows and columns, and I usually want no subtotals at all, and if I do want subtotals, I usually only want column totals, or sometimes only row totals.
  4. The columns have all been autofitted, so the first two columns are very wide, and all columns have different widths. When the Pivot Table is refreshed, these widths are subject to change due to the autofitting.

So I have to go to the Pivot Table ribbon tab and fix the first three problems, and to Pivot Table Options to fix the column autofitting. Yeah, big deal, it’s only 30 seconds, but it’s 30 seconds each, and it’s easy to forget to fix them all, and anyway, it’s a pain.

For all those years I was merely annoyed about Excel’s Pivot Table default settings, Bill Mr Excel Jelen went further, and lobbied with every Excel user and every Microsoft Excel product team member to change them. For years this had no effect, other than he became well known within the Excel product group.

But then Microsoft introduced User Voice, an online forum that lets users suggest new features and improvements to existing features, and vote on other suggestions. Microsoft reads all of the suggestions, and those suggestions with the most votes are considered for inclusion in their product.

Bill submitted Allow users to set/choose defaults (via settings) for PivotTables – Layout (compact, tabular, classic, etc.), PT Options, etc., and it began piling up the votes.

Two weeks ago, the official Excel Team blog contained an article entitled PivotTables just got personal. Long story short, they’ve implemented Bill’s suggestion, and in a very flexible way. Instead of simply changing the Pivot Table default layout back to Tabular, they allow users to set all Pivot Table options to create a custom Pivot Table default. This feature is available to all Excel 2016 users with Office 365, as long as they have gotten the recent updates.

Here’s how to change your Pivot Table default settings. On Excel’s File tab, select Excel Options, and click on Data in the Excel Options dialog.

Data Tab of Excel Options Dialog

Click the Edit Default Layout button to open the Edit Default Layout button.

Edit Default Layout Dialog

Layout Import uses the settings of an existing Pivot Table as the new custom default layout (I happened to have cell S21 selected when I opened the dialog; this cell was not in any Pivot Table). You can start with that, or simply make any changes in the dialog. You can see that I’ve already fixed them all.

You can even click the Pivot Table Options button to access any settings in the entire Pivot Table Options dialog, like that pesky Autofit Column Widths setting.

Pivot Table Options Dialog

After correcting my Pivot Table default settings, when I insert a pivot table, here is the absolute first view of the result. I don’t need to change anything!

Pivot Table with Custom Pivot Table Default Settings

Thanks Microsoft, thanks User Voice, and thanks Mr Excel for his tireless whining lobbying to make this improvement to Excel. I hope this Pivot Table Default Layout feature becomes the model for how users interact with all Excel defaults in the future.

Posted: Wednesday, May 31st, 2017 under Pivot Tables.
Tags: , .
Comments: 21

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  1. Doug Glancy says

    Hi Jon,

    That is a nice addition. My version of Office 365 says it’s fully updated but doesn’t show these changes yet. There isn’t a separate Data option yet – it’s still under Options>Advanced. The version is 1609 (Build 7369.2130).

    So, at least for now, I’ll hold on to this code:

    With pvt
    'Tabular report layout
    .RowAxisLayout xlTabularRow
    .ShowDrillIndicators = False
    .TableStyle2 = ""
    'Turn off "resize cols on refresh"
    .HasAutoFormat = False
    For Each pvtField In .PivotFields
        On Error Resume Next
        pvtField.IncludeNewItemsInFilter = True
        On Error GoTo SetUpPivot_error_handler
        pvtField.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Next pvtField
End With

  2. Larry Caretto says

    I think it would be useful to allow users to set the default options for all setup choices. I have one in particular that I always have to change. The default display of a “zero-value” such as a null string generated by a formula is a numerical zero. I often have this occur and have to reset the option to display a blank cell. It would be nice if I could set the default for this option to be the one I want.

  4. Jon Peltier says

    Larry –
    I made a User Voice suggestion to introduce a NULL() worksheet function which would return an actual null, so the cell would be treated as a blank cell. There is no way to do this, although different workarounds can be used for different circumstances.
    What is the option for displaying a cell as a blank cell? You can choose what to show in a pivot table if a cell is blank or has an error value, but that seems opposite to what you describe. Also, you have options for how a chart should display a blank cell, but it only works for a truly blank cell, not for anything you would return from a function that looks blank.

  5. Jeff Weir says

    I don’t have this option yet either, and Excel too tells me I am fully up-to-date. I have Version 1701 (Build 7766.2084) , First Release for Deferred Channel.

    Meanwhile I still use my InstantPivot routine, from http://dailydoseofexcel.com/archives/2014/02/12/instant-pivot-just-add-water/

    This makes a Pivottable out of it at the edge of the used range, applies my preferred default settings, turns the source data into an Excel Table automatically, and then selects the Pivot and cuts it, so that I can paste it somewhere else with a simple Ctrl + V if I want. And if I run it again on an existing PivotTable it adopts the source formatting for any fields that are in the PivotTable, plus gets rid of those annoying ‘Sum of’ prefixes.

    While this new functionality my MS is great, it will have been an opportunity lost if MS still don’t let Pivots automatically adopt the source formatting of the underlying fields.

  7. Jon Peltier says

    Jeff –

    So Deferred Channel is the slowest to update in Office 365, with updates every four months; it’s mostly for corporations with timit IT departments. First Release for Deferred Channel is a bit quicker, giving you the monthly Current Channel updates.

    Current Channel is the “standard” update schedule, with updates every month. These updates have been through rigorous testing, from dogfood (i.e., within Microsoft) through the Office Insider options (below). Current Channel subscribers should have the Pivot Table updates by now. I have Current Channel on a different computer, but not the Office 365 version. That one doesn’t have the Pivot Table update, because Microsoft only gives the cool new features to Office 365 subscribers.

    Office Insider is how people can volunteer to do this testing. You get features before everyone else, sometimes before the features are totally ready for prime time. So you let them know about any glitches, and they’ll be fixed in time for Current Channel release.

    Within Office Insider, there is Slow Release, where most of these glitches have already been weeded out, and there is Fast Release, which is where we guinea pigs hang out.

  8. Jon Peltier says

    Jeff –

    It would be a great improvement if Pivot Table value fields adopted the number formats of their underlying fields.

    I also hate that a blank cell changes a value field from ‘Sum of’ to ‘Count of’. Text values, sure, but blanks? Shouldn’t happen.

  9. Doug Glancy says

    Jon, Thanks for the explanation of the channels. I’m on deferred at home and work (got my home Office through my daughter’s university, which I appreciate greatly).

  10. Jesse says

    Jon – This is great! Thank you for sharing. I’m so glad that we no longer have to go through the tedious process of setting up a new PivotTable. The time savings from this will certainly add up.

    On a similar note, is there any way to set the default number format on the “Value Field Settings” menu? I would like to set the default number format to “Accounting”.

    Thanks for the quality content!

  11. Jon Peltier says

    Jesse –
    I don’t think you can preset the format for a pivot value field. But my colleague Jeff Weir has posted a routine he used to insert a pivot table which applied all of his default settings (such as the new feature described her does), but also a couple other things, including set the number formats to match those in the source data. You can find his routine here:
    Instant Pivot: Just Add Water

  13. Jeff Weir says

    Good to hear, Jesse. Note that I’ve just slightly changed the code by removing the .SaveData = False line, as I got sick of getting prompted to refresh the PivotTable before being able to filter it. You can download the new code if you like from the original link Jon posted.

    I have this assigned to a Ctrl + Shift + I shortcut, and use it all the time.

  16. Jon Peltier says

    Doug –

    If you’re not using Office 365, you will not see the new features. The Office 365 Current Channel is up to 1705. I guess there’s a Deferred option that’s further behind.

  17. Jeff Weir says

    Jon: Re your comment “I also hate that a blank cell changes a value field from ‘Sum of’ to ‘Count of’. Text values, sure, but blanks? Shouldn’t happen.” Now it doesn’t anymore. Not sure if this is another update, or part of the one above. But hip hip hooray Microsoft :=)

  18. Jon Peltier says

    That’s cool. And this is native Excel, right, not one of your add-ins? Because it would be the third time in the last couple weeks that someone told me, hey they fixed that, or they added that, and it was something third-party.

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel