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.
What’s wrong with this Pivot Table?
- 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.
- There are subtotals, and I usually do not want subtotals.
- 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.
- 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.
Click the Edit Default Layout button to open the Edit Default Layout button.
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.
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!
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.
Jon Acampora writes the blog at 
