SETTING PIVOT TABLE DEFAULTS
In the past, pivot tables were created in the Compact layout shown in Figure 1. Multiple fields in the Rows area are all collapsed into column A with a generic heading of “Row Labels.” Empty cells appear in the pivot table as blank instead of zero. Subtotals appear at the top of each group instead of the bottom.
The new feature lets you set the default layout for your pivot tables. There are two ways to do this. Go to File, Options, Advanced. Scroll to the Data section. The first item should be Make Changes to the Default Layout of Pivot Tables. Click the button for Edit Default Layout.
Open the Report Layout dropdown and choose Show in Tabular Form. Choose the checkbox for Repeat All Item Labels. Change the Subtotals option to Show Subtotals At the Bottom of the Group. Click the PivotTable Options button. In the PivotTable Options dialog, type a zero (0) for the setting called For Empty Cells, Show.
The next time that you create a pivot table, your settings will appear in the pivot table as shown in Figure 2. Note that you now have useful headings in A4, B4, and C3. Totals appear at the bottom of each group. Empty cells are replaced by zero.
The second way to set the defaults is useful if you have a pivot table that’s already in the correct format. You can base the defaults on that pivot table. Open the workbook that contains the pivot table. Select one cell in the pivot table. Go to File, Options, Advanced, Data, and click the button for Edit Default Layout.
Use the Layout Import feature by entering a single cell from the pivot table in Layout Import and clicking the Import button. All of the settings from the pivot table will become the default for future pivot tables.
EXCEL.USERVOICE.COM
If you use Excel 40 hours a week, you probably have a small wish list of things that you wished were different about it. With Excel.UserVoice.com, you can communicate those ideas directly to the Excel team. If others agree with you and vote for your idea—an idea needs at least 20 votes before the Excel team will respond—it’s possible that your idea could be incorporated in a future monthly release of Office 365.
This new pivot table feature began as an idea I posted at Excel.UserVoice.com. I had been grumbling about the Compact layout introduced in Excel 2007 for years and wished that Microsoft would let me specify that all future pivot tables would start in Tabular layout instead of Compact. And while conducting seminars for IMA® (Institute of Management Accountants) chapters, I would encounter members in the audience asking for all pivot tables to start in Classic Mode or various other requests. So I submitted my idea to Excel.UserVoice.com in early 2016 and asked others to vote for it. The idea had already received a few hundred votes before the Excel team indicated that they were going to implement a larger version of it.
ONLY THROUGH OFFICE 365
Microsoft used to release a new version of Office every two to three years. This development cycle was slow. If you had a great idea just after Excel 2013 shipped, you would have to wait three years for the next release of Office. Instead of paying $400 every three years for the next release of Office, Microsoft’s new strategy is that people will pay $10 a month for a continually updated version of Office. This product is called Office 365.
New features could come out every Tuesday, but realistically, a new feature might arrive once a month. Other features that are only in Office 365 include the funnel chart, map chart, MAXIFS function, TEXTJOIN function, and icons. Other small improvements, such as the black theme, are also released this way. Those of you who create CSV files might have noticed that Excel would nag you twice during the Save As process that you might lose features with that file format. If you’re a CSV pro, it could be rather annoying to be nagged twice. A recent update to Office 365 reduces the nagging by 50%.
Features like this take 13 weeks to roll out through Office 365. The Pivot Table Default feature was rolled out to the Office 365 Insider Fast channel during the first week of March 2017. If you need the feature today, search for “How to Become Office Insider Fast” for information on changing your subscription plan.
SF SAYS
The Pivot Table Default feature allows you to specify the Classic pivot table layout or any other setting found in the Pivot Table Options dialog.
April 2017
