MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Specify Defaults for All Future Pivot Tables

May 13, 2024 - by

Excel 2024: Specify Defaults for All Future Pivot Tables

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.
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.

continue reading »

More Reading

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

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

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

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.

read more articles »

Featured Products

Microsoft 365 Excel: The Only App That Matters

Microsoft 365 Excel: The Only App That Matters

June 2022

Excel Worksheet, Power Query, Power Pivot, Power BI. Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight!

Your Excel Survival Kit - Second Edition

Your Excel Survival Kit - Second Edition

March 2021

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

MrExcel 2021 - Unmasking Excel

MrExcel 2021 - Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

Guerrilla Data Analysis Using Microsoft Excel - 3rd Edition

Guerrilla Data Analysis Using Microsoft Excel - 3rd Edition

May 2022

Two of the leading Excel channels on YouTube join forces to combat bad data. This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, "MrExcel," and Oz do Soleil during their careers run as financial analysts.

see more products »