Excel 2024: Specify Defaults for All Future Pivot Tables
May 13, 2024 - by Bill Jelen
![Excel 2024: Specify Defaults for All Future Pivot Tables Excel 2024: Specify Defaults for All Future Pivot Tables](jpg/excel-2024-specify-defaults-for-all-future-pivot-tables.jpg)
It took me six years, but I finally convinced the Excel team that a lot of people prefer Tabular layout for pivot tables to the Compact layout that became the default layout in Excel 2007. If you have Microsoft 365, you now have the ability to specify pivot table defaults.
Go to File, Options, Data. Click Edit Default Layout.
![In Excel Options, look for the new Data category in the left navigation bar. It should be third, right after General and Formulas. If you have the category, then the first choice is Make Changes To The Default Layout of Pivot Tables.](png/liv26.png)
Change the Report Layout to Show in Tabular Form and choose the checkbox Repeat All Item Labels.
Tip
There are other settings that you can specify as the default. You can either click Pivot Table Options and specify them or find a pivot table where you've already set up your favorite settings. Select one cell in that pivot table and click Import.
If you don't have Microsoft 365 and don't have access to pivot table defaults, you can get similar functionality by buying Pivot Power Premium from Debra Dalgleish at Contextures.com.
Bonus Tip: Change What Drives You Crazy About Excel
I've managed to lobby the Excel team to get a few changes into Excel. It isn't always easy. It took me eight years of lobbying to get the Repeat All Item Labels feature added to Excel 2010. It took seven years to get the Pivot Table Defaults feature added.
The old Excel.UserVoice.com site has been migrated to feedbackportal.microsoft.com. Search for Excel and then filter the Platform to Windows or Mac. If you have a great idea of what would make Excel easier, write up a short post here. And then get your friends and co-workers to vote for your idea. The great news is that the Excel team is listening to ideas. The key to getting the Excel team to respond is 20 votes. But I managed 200-300 votes for my Pivot Table Defaults before they started working on the feature.
This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by Volodymyr Hryshchenko on Unsplash