Excel 2024: Specify Defaults for All Future Pivot Tables

May 13, 2024 - by Bill Jelen

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.

Change the Report Layout to Show in Tabular Form and choose the checkbox Repeat All Item Labels.

More Reading

Excel 2024: Find the True Top Five in a Pivot Table »

May 10, 2024 - by Bill Jelen

Pivot tables offer a Top 10 filter. It is cool. It is flexible. But I hate it, and I will tell you why.

Excel 2024: Change the Calculation in a Pivot Table »

May 8, 2024 - by Bill Jelen

Pivot tables offer a myriad of calculations in the Field Settings dialog box. Here is a faster way to change a calculation.

Excel 2024: Create a Year-over-Year Report in a Pivot Table »

May 6, 2024 - by Bill Jelen

Let's say you have two years worth of detail records. Each record has a daily date. When you build a pivot table from this report, you will have hundreds of rows of daily dates in the pivot table.

Financial Modelling in Power BI

September 2022

The only book to produce all three financial statements in Power BI.

Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

April 2022

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won't find anywhere else, and create automated reports that are amazingly powerful.

Master Your Data with Power Query in Excel and Power BI

August 2021

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Power BI for the Excel Data Analyst

July 2022

A step-by-step guide on how to use Power BI. Written with Excel fans in mind, but with every Power BI student at its heart.

see more products »

Excel 2024: Specify Defaults for All Future Pivot Tables

More Reading

Featured Products